如何查询SQL表中指定日期与班次下缺失的machine记录

实现逻辑 #

核心思路是先生成所有符合要求的日期-班次-设备预期组合，再和实际录入的业务表左连接，匹配不到的记录即为漏录条目。

完整SQL代码（兼容MySQL 8+、SQL Server、PostgreSQL等支持CTE的数据库） #

WITH 
-- 1. 生成设备基准列表（固定为TG01~TG010，若有单独设备字典表可直接替换此部分）
machines AS (
    SELECT 'TG01' AS machine UNION ALL
    SELECT 'TG02' UNION ALL SELECT 'TG03' UNION ALL
    SELECT 'TG04' UNION ALL SELECT 'TG05' UNION ALL
    SELECT 'TG06' UNION ALL SELECT 'TG07' UNION ALL
    SELECT 'TG08' UNION ALL SELECT 'TG09' UNION ALL
    SELECT 'TG010'
),
-- 2. 生成班次基准列表（根据实际班次数量调整，若有班次字典表可直接替换此部分）
shifts AS (
    SELECT 1 AS shift UNION ALL SELECT 2
),
-- 3. 提取所有需要校验的日期，也可手动指定日期范围
all_dates AS (
    SELECT DISTINCT `date` FROM your_table_name
    -- 若需要限定查询时间范围可加过滤条件，示例为MySQL语法查最近7天：
    -- WHERE STR_TO_DATE(`date`, '%d/%m/%Y') >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
),
-- 4. 生成全量预期记录组合
expected_records AS (
    SELECT d.`date`, s.shift, m.machine
    FROM all_dates d
    CROSS JOIN shifts s
    CROSS JOIN machines m
)
-- 5. 匹配实际记录，筛选漏录条目
SELECT 
    e.`date`, 
    e.shift, 
    e.machine AS missing_machine
FROM expected_records e
LEFT JOIN your_table_name t 
    ON e.`date` = t.`date` 
    AND e.shift = t.shift 
    AND e.machine = t.machine
WHERE t.data_id IS NULL
ORDER BY e.`date`, e.shift, e.machine;

代码使用前请将your_table_name替换为实际业务表的表名。

PowerBI适配说明 #

• 可以直接将上述SQL作为PowerBI的数据源导入，每次刷新数据集时都会自动计算最新的漏录记录，无需人工每日核对
• 导入后可直接在PowerBI中添加日期、班次筛选器，按需查看对应范围的漏录情况

注意事项 #

• 如果已维护独立的设备字典表、班次字典表，可直接替换对应CTE部分的代码，无需手动枚举设备/班次，后续规则变动时仅需修改字典表即可
• 不同数据库的日期处理函数存在差异，请根据使用的数据库类型调整日期转换、时间范围过滤的相关语法
• 若需要限定只校验特定用户提交的数据，可在LEFT JOIN的关联条件或WHERE子句中增加user字段的过滤规则

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