SQL查询需求：每日展示所有设备交易记录，无交易补NULL值

你的问题核心在于原左连接只关联了已有交易记录的设备和日期组合，没有生成“每日每台设备”的基础行，所以当某台设备某天没有交易时，就不会出现在结果里。要实现需求，我们需要先构建一个包含所有目标日期和所有设备的全量基础数据集，再和交易表做左连接。

核心思路 #

• 生成需要展示的所有日期范围（比如你需要的每日日期）
• 将日期范围与def表做交叉连接（CROSS JOIN），得到每一天每台设备的基础记录
• 左连接event表，匹配设备ID和日期；无交易时用NULL填充交易字段，日期列用当月首日替代

SQL实现（以SQL Server为例） #

-- 1. 生成目标日期范围（示例为2024年1月整月，可按需调整起止日期）
WITH date_range AS (
    SELECT CAST('2024-01-01' AS DATE) AS report_date
    UNION ALL
    SELECT DATEADD(day, 1, report_date)
    FROM date_range
    WHERE report_date < CAST('2024-01-31' AS DATE)
),
-- 2. 生成日期-设备的全量组合
device_dates AS (
    SELECT 
        d.Id,
        d.Device,
        d.Location,
        dr.report_date
    FROM def d
    CROSS JOIN date_range dr
)
-- 3. 左连接交易表，处理空值和日期
SELECT 
    dd.Id,
    dd.Device,
    dd.Location,
    e.eventid,
    e.deviceid,
    e.event,
    -- 有交易用交易日期，无交易用当月首日
    COALESCE(e.date, DATEFROMPARTS(YEAR(dd.report_date), MONTH(dd.report_date), 1)) AS date
FROM device_dates dd
LEFT JOIN event e 
    ON dd.Id = e.deviceid 
    AND dd.report_date = CAST(e.date AS DATE) -- 确保日期匹配（去除时间部分）
ORDER BY dd.report_date, dd.Id;

关键细节说明 #

• 交叉连接（CROSS JOIN）：这一步是核心，它会把每个设备和每个日期进行组合，保证每一天每台设备都有一条基础记录，解决了原语句缺失无交易设备行的问题。
• 日期处理：用COALESCE函数判断，如果event.date存在就用它，否则生成当月首日（不同数据库的当月首日生成函数略有差异）。
• 日期匹配：将e.date转为DATE类型，避免时间部分（比如2024-01-05 14:30:00）干扰日期匹配。

其他数据库适配示例 #

MySQL版本 #

WITH date_range AS (
    SELECT STR_TO_DATE('2024-01-01', '%Y-%m-%d') AS report_date
    UNION ALL
    SELECT DATE_ADD(report_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE report_date < STR_TO_DATE('2024-01-31', '%Y-%m-%d')
),
device_dates AS (
    SELECT 
        d.Id,
        d.Device,
        d.Location,
        dr.report_date
    FROM def d
    CROSS JOIN date_range dr
)
SELECT 
    dd.Id,
    dd.Device,
    dd.Location,
    e.eventid,
    e.deviceid,
    e.event,
    COALESCE(e.date, DATE_FORMAT(dd.report_date, '%Y-%m-01')) AS date
FROM device_dates dd
LEFT JOIN event e 
    ON dd.Id = e.deviceid 
    AND DATE(e.date) = dd.report_date
ORDER BY dd.report_date, dd.Id;

PostgreSQL版本 #

WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::DATE, 
        '2024-01-31'::DATE, 
        '1 day'::INTERVAL
    )::DATE AS report_date
),
device_dates AS (
    SELECT 
        d.Id,
        d.Device,
        d.Location,
        dr.report_date
    FROM def d
    CROSS JOIN date_range dr
)
SELECT 
    dd.Id,
    dd.Device,
    dd.Location,
    e.eventid,
    e.deviceid,
    e.event,
    COALESCE(e.date, DATE_TRUNC('month', dd.report_date)::DATE) AS date
FROM device_dates dd
LEFT JOIN event e 
    ON dd.Id = e.deviceid 
    AND e.date::DATE = dd.report_date
ORDER BY dd.report_date, dd.Id;

优化建议 #

如果你的数据库中有现成的日历表（专门存储所有日期的表），可以用它替代递归生成的date_range CTE，性能会更优，尤其是当日期范围很大时。

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