你能帮帮我吗?
需要将行值转换为具有按日期分组的列
select DATE(TimeStamp) as date ,Status ,count(Status) as value from abc where (TIMESTAMP BETWEEN '2017-01-00 00:00:00' and '2018-10-09 23:59:59') AND Name='xyz' GROUP BY DATE(TimeStamp),Status limit 15;
产量
+------------+--------------+-------+
| date | Status | value |
+------------+--------------+-------+
| 2017-08-09 | running | 193 |
| 2017-08-09 | stop | 726 |
| 2017-08-09 | UnknownError | 814 |
| 2017-08-10 | running | 417 |
| 2017-08-10 | stop | 1584 |
| 2017-08-10 | UnknownError | 1776 |
| 2017-08-11 | running | 419 |
| 2017-08-11 | stop | 1584 |
| 2017-08-11 | UnknownError | 1776 |
| 2017-08-12 | running | 418 |
| 2017-08-12 | stop | 1584 |
| 2017-08-12 | UnknownError | 1776 |
| 2017-08-13 | running | 420 |
| 2017-08-13 | stop | 1584 |
| 2017-08-13 | UnknownError | 1776 |
+------------+--------------+-------+
预期产出:
+------------+--------------+---------+--------------+
| date | stop | running |UnknownError |
+------------+--------------+---------+--------------+
| 2017-08-09 | 726 | 193 |814 |
+------------+--------------+---------+--------------+
1 回答
看看把你的查询变成一个PIVOT .
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017
和Convert Rows to columns using 'Pivot' in SQL Server的欺骗