MySQL 5.5 - 在 SELECT 中减少表

Question

我有一个简单的表格和有趣的任务要做。我想从最初的开始减少表格

+----------+------------+--------+------------+--------+------------+
| NoClosed | Closed     | NoOpen | Open       | NoPlan | Plan       |
+----------+------------+--------+------------+--------+------------+
| 1        | 2018-10-23 | NULL   | NULL       | NULL   | NULL       |
| 2        | 2018-10-22 | NULL   | NULL       | NULL   | NULL       |
| 1        | NULL       | 1      | 2018-10-23 | NULL   | NULL       |
| NULL     | NULL       | 10     | 2018-10-25 | NULL   | NULL       |
| NULL     | NULL       | NULL   | NULL       | 1      | 2018-10-27 |
| NULL     | NULL       | NULL   | NULL       | 1      | 2018-10-28 |
| 10       | 2018-10-17 | NULL   | NULL       | NULL   | NULL       |
| 1        | NULL       | 1      | 2018-10-18 | NULL   | NULL       |
+----------+------------+--------+------------+--------+------------+

给这个:

Date    NoClosed    NoOpen  NoPlan
17/10/2018  10      
18/10/2018          
19/10/2018          
20/10/2018          
21/10/2018          
22/10/2018  2       
23/10/2018  3         1 
24/10/2018          
25/10/2018            10    
26/10/2018          
27/10/2018                   1
28/10/2018                   1
29/10/2018          
30/10/2018          
31/10/2018

它将从 -7 天到 +7 天，但是我无法在选择中创建包含这些日期的列。我想我需要它为所有数据设置一个枢轴点，然后对其进行计数和分组。

我的表格代码在这里，任何想法将不胜感激。

CREATE TABLE Orders
(
NoClosed VARCHAR(20), 
Closed DATE, 

NoOpen VARCHAR(20),
Open DATE,

NoPlan VARCHAR(20), 
Plan DATE);




insert into Orders values (1,       "2018-10-23",   NULL,   NULL,   NULL, NULL);    
insert into Orders values (2,       "2018-10-22",   NULL,   NULL,   NULL, NULL);    

insert into Orders values (1,       NULL,       1,  "2018-10-23",   NULL,   NULL);
insert into Orders values (NULL,    NULL,       10, "2018-10-25",   NULL,   NULL);

insert into Orders values (NULL,    NULL,       NULL,   NULL,   1, "2018-10-27");   
insert into Orders values (NULL,    NULL,       NULL,   NULL,   1, "2018-10-28");
insert into Orders values (10,      "2018-10-17",   NULL,   NULL,   NULL, NULL);
insert into Orders values (1,       NULL,       1,  "2018-10-18",   NULL,   NULL);

Answer 1

参见 What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?了解如何创建包含日期范围内所有日期的表。然后将其与一个对您的数据进行透视的查询结合起来:

SELECT d.date,
    MAX(CASE WHEN o.closed = date THEN o.NoClosed END) AS NoClosed,
    MAX(CASE WHEN o.open = date THEN o.NoOpen END) AS NoCLosed,
    MAX(CASE WHEN o.plan = date THEN o.NoPlan END) AS NoPlan
FROM DateTable AS d
LEFT JOIN Orders ON d.date IN (o.closed, o.open, o.plan)
GROUP BY d.date