SQL查询：如何返回缺失日期并标记Quality为“Missing”（仅读权限）

由于你只有只读权限无法创建临时表，可通过生成日期序列+左连接的方式实现需求，以下是主流数据库的具体实现：

Oracle #

利用CONNECT BY生成目标日期范围的所有日期，再左连接原表填充缺失值：

WITH date_range AS (
    SELECT TRUNC(TO_DATE('1-AUG-2022', 'DD-MON-YYYY')) + LEVEL - 1 AS date_val
    FROM dual
    CONNECT BY LEVEL <= TRUNC(TO_DATE('5-AUG-2022', 'DD-MON-YYYY')) - TRUNC(TO_DATE('1-AUG-2022', 'DD-MON-YYYY')) + 1
)
SELECT 
    dr.date_val AS "Date",
    'MeterX' AS "Meter",
    NVL(mdb.Quality, 'Missing') AS "Quality"
FROM date_range dr
LEFT JOIN MyDB mdb 
    ON dr.date_val = mdb."Date" 
    AND mdb."Meter" = 'MeterX'
ORDER BY dr.date_val;

• 用TRUNC确保日期是无时间部分的纯日期值，匹配原表的Date列类型
• NVL函数将空值替换为Missing

SQL Server #

通过递归CTE生成日期序列，左连接后处理缺失值：

WITH date_range AS (
    SELECT CAST('2022-08-01' AS DATE) AS date_val
    UNION ALL
    SELECT DATEADD(DAY, 1, date_val)
    FROM date_range
    WHERE date_val < CAST('2022-08-05' AS DATE)
)
SELECT 
    dr.date_val AS "Date",
    'MeterX' AS "Meter",
    ISNULL(mdb.Quality, 'Missing') AS "Quality"
FROM date_range dr
LEFT JOIN MyDB mdb 
    ON dr.date_val = mdb."Date" 
    AND mdb."Meter" = 'MeterX'
ORDER BY dr.date_val
OPTION (MAXRECURSION 0); -- 日期范围超过100天时必须添加，解除递归次数限制

PostgreSQL #

使用generate_series快速生成日期序列，语法更简洁：

SELECT 
    dr.date_val AS "Date",
    'MeterX' AS "Meter",
    COALESCE(mdb.Quality, 'Missing') AS "Quality"
FROM generate_series(
    '2022-08-01'::DATE, 
    '2022-08-05'::DATE, 
    '1 day'::INTERVAL
) dr(date_val)
LEFT JOIN MyDB mdb 
    ON dr.date_val = mdb."Date" 
    AND mdb."Meter" = 'MeterX'
ORDER BY dr.date_val;

• generate_series直接生成指定区间的连续日期，无需递归或层级查询

内容的提问来源于stack exchange，提问作者dohan_rivas