mysqli - 即使 SQL Server 中也不存在数据，如何在一个表中获取所有日期的日期范围

Question

我有一个名为 Tab1 的表名。即使缺少任何一天，我也想获得所有日期。

 +-------------------+--------------------------+
 |Name               | dateCheck                |
 +-------------------+--------------------------+
 | 1                 | 2016-01-01 00:00:00.000  |
 | 2                 | 2016-01-02 00:00:00.000  |
 | 3                 | 2016-01-05 00:00:00.000  |
 | 4                 | 2016-01-07 00:00:00.000  |
 +-------------------+--------------------------+
I need output like below :
 +-------------------+--------------------------+
 |Name               | dateCheck                |
 +-------------------+--------------------------+
 | 1                 | 2016-01-01 00:00:00.000  |
 | 2                 | 2016-01-02 00:00:00.000  |
 | 0                 | 2016-01-03 00:00:00.000  |
 | 0                 | 2016-01-04 00:00:00.000  |
 | 3                 | 2016-01-05 00:00:00.000  |
 | 0                 | 2016-01-06 00:00:00.000  |
 | 4                 | 2016-01-07 00:00:00.000  | 

标签： mysqli

Answer 1

您可以使用日历表：

SELECT
    COALESCE(t2.Name, 0) AS Name,
    t1.dateCheck
FROM
(
    SELECT '2016-01-01' AS dateCheck UNION ALL
    SELECT '2016-01-02' UNION ALL
    SELECT '2016-01-03' UNION ALL
    SELECT '2016-01-04' UNION ALL
    SELECT '2016-01-05' UNION ALL
    SELECT '2016-01-06' UNION ALL
    SELECT '2016-01-07'
) t1
LEFT JOIN yourTable t2
    ON t1.dateCheck = t2.dateCheck;