MySQL 查询、数据透视表

Question

我在构建将行转换为列的查询时遇到问题。我的枢轴是我的日期。用户需要的是查看整周的数据。

我的数据是这样构建的:

ID - AssemblyDate
----------------------------------
12345 - 2015-01-01
12346 - 2015-01-01
12347 - 2015-01-01
12348 - 2015-01-02
12349 - 2015-01-02
12350 - 2015-01-02
12351 - 2015-01-03
12352 - 2015-01-03
12353 - 2015-01-03

我期望的结果会是这样的

DAY1 - DAY2 - DAY3
12345  12348  12351
12346  12349  12352
12347  12350  12352

我尝试过:

SELECT CASE WHEN (AssemblyDate = '2015-01-01') THEN ID ELSE NULL END AS DAY1
       , CASE WHEN (AssemblyDate = '2015-01-02') THEN ID ELSE NULL END AS DAY2
       , CASE WHEN (AssemblyDate = '2015-01-03') THEN ID ELSE NULL END AS DAY3
FROM MyTable
GROUP BY AssemblyDate

这给了我这样的东西:

DAY1 - DAY2 - DAY3
12345  NULL   NULL
12346  NULL   NULL
12347  NULL   NULL
NULL   12348  NULL
NULL   12349  NULL
NULL   12350  NULL
NULL   NULL   12351
NULL   NULL   12352
NULL   NULL   12352

但我不想要空值...有没有办法可以将它们分组而不需要空值？

Answer 1

您需要做一些调整，因为您需要进一步对这些结果进行分组。由于没有任何东西可以对它们进行分组，因此我们需要设计一些用于分组的东西 - 在这种情况下，每列一个变量，只要该列与组装日期匹配，我们就会增加该变量:

select if(assemblydate = '2015-01-01', @d1c := @d1c +1, 
          if(assemblydate = '2015-01-02', @d2c := @d2c + 1, @d3c := @d3c +1)) id,  
       case when assemblydate = '2015-01-01' then id end d1,   
       case when assemblydate = '2015-01-02' then id end d2,   
       case when assemblydate = '2015-01-03' then id end d3  
  from d, (select @d1c := 0, @d2c := 0, @d3c := 0) q;

该查询仅添加一个 id 列，该列每天都会递增，结果如下所示。

+------+-------+-------+-------+
| id   | d1    | d2    | d3    |
+------+-------+-------+-------+
|    1 | 12345 |  NULL |  NULL |
|    2 | 12346 |  NULL |  NULL |
|    3 | 12347 |  NULL |  NULL |
|    1 |  NULL | 12348 |  NULL |
|    2 |  NULL | 12349 |  NULL |
|    3 |  NULL | 12350 |  NULL |
|    1 |  NULL |  NULL | 12351 |
|    2 |  NULL |  NULL | 12352 |
|    3 |  NULL |  NULL | 12352 |
+------+-------+-------+-------+

完成后，我们只需围绕结果包装另一个查询，按 id 分组并使用 max() 选取不为 null 的值

select max(d1), max(d2), max(d3) from
(
  select if(assemblydate = '2015-01-01', @d1c := @d1c +1, 
          if(assemblydate = '2015-01-02', @d2c := @d2c + 1, @d3c := @d3c +1)) id,  
       case when assemblydate = '2015-01-01' then id end d1,   
       case when assemblydate = '2015-01-02' then id end d2,   
       case when assemblydate = '2015-01-03' then id end d3  
  from d, (select @d1c := 0, @d2c := 0, @d3c := 0) q
) qq
group by id;

fiddle here