mysql - 如何对处于 "full line"模式 SQL 的数据进行分组和重新排列

Question

我将 SQL 数据存储在“整行”中，需要重新排列
原始数据

ID      User    Date            Act1   Act2  Act3  Act4 
1       Aba     2019-04-27      3      1     NULL  2
2       Bob     2019-04-28      1      NULL  2     1
3       Carl    2019-04-28      2      1     1     2
4       Dan     2019-04-29      3      2     1     NULL
5       Aba     2019-04-29      4      7     5     NULL

期望状态(每天和行为)

ID      2019-04-27    2019-04-28   2019-04-29
Act1       3          3            7
Act2       1          1            9
Act3      NULL        3            6
Act4       2          3            NULL

任何指导将不胜感激，谢谢

Answer 1

您可以尝试使用具有条件加重功能的UNION ALL。

查询 1:

SELECT 'Act1' Id,
       SUM(CASE WHEN Date = DATE'2019-04-27' THEN Act1 END) '2019-04-27',
       SUM(CASE WHEN Date = DATE'2019-04-28' THEN Act1 END) '2019-04-28',
       SUM(CASE WHEN Date = DATE'2019-04-29' THEN Act1 END) '2019-04-29'
FROM T
UNION ALL
SELECT 'Act2' Id,
       SUM(CASE WHEN Date = DATE'2019-04-27' THEN Act2 END) '2019-04-27',
       SUM(CASE WHEN Date = DATE'2019-04-28' THEN Act2 END) '2019-04-28',
       SUM(CASE WHEN Date = DATE'2019-04-29' THEN Act2 END) '2019-04-29'
FROM T
UNION ALL
SELECT 'Act3' Id,
       SUM(CASE WHEN Date = DATE'2019-04-27' THEN Act3 END) '2019-04-27',
       SUM(CASE WHEN Date = DATE'2019-04-28' THEN Act3 END) '2019-04-28',
       SUM(CASE WHEN Date = DATE'2019-04-29' THEN Act3 END) '2019-04-29'
FROM T
UNION ALL
SELECT 'Act4' Id,
       SUM(CASE WHEN Date = DATE'2019-04-27' THEN Act4 END) '2019-04-27',
       SUM(CASE WHEN Date = DATE'2019-04-28' THEN Act4 END) '2019-04-28',
       SUM(CASE WHEN Date = DATE'2019-04-29' THEN Act4 END) '2019-04-29'
FROM T

Results :

|   Id | 2019-04-27 | 2019-04-28 | 2019-04-29 |
|------|------------|------------|------------|
| Act1 |          3 |          3 |          7 |
| Act2 |          1 |          1 |          9 |
| Act3 |     (null) |          3 |          6 |
| Act4 |          2 |          3 |     (null) |