mysql - 如何根据日期列对此查询进行右连接？

Question

如何对此查询进行右连接。我有另一个表，我需要根据日期右连接此查询结果。

(SELECT t.date FROM (SELECT ("2018-01-31" - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM 
(SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  
WHERE c.number BETWEEN 0 and 30) );

新建查询，这是添加左连接后的查询

    select date from(SELECT d.date FROM (SELECT ("2018-01-31" - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM 
(SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  
WHERE c.number BETWEEN 0 and 30))d LEFT JOIN report r ON r.date = d.date;

Answer 1

使用左连接。然后你只需这样做:

select d.date, . . . 
from (<your query here>) d left join
     yourtable t
     on t.date = d.date