SQL Server 2017如何用单条查询获取含今日更新的主记录下所有关联详情

实现逻辑 #

• 第一步：筛选出所有今日有详情记录更新的主表ID，提前去重避免同个主表下多条今日更新记录导致结果重复
• 第二步：将符合条件的主表ID关联Master主表、全量Details详情表，即可得到对应主表下的所有详情记录+主表信息，天然满足去重要求

具体SQL代码 #

写法1：EXISTS实现（性能更优，无需额外去重步骤） #

SELECT 
    m.id AS master_id,
    d.id AS details_id,
    m.name AS master_name,
    d.info,
    d.update_date
FROM Master m
INNER JOIN Details d ON m.id = d.master_id
WHERE EXISTS (
    SELECT 1 
    FROM Details d2 
    WHERE d2.master_id = m.id
    -- 若update_date为DATE类型可去掉CAST，直接等于CAST(GETDATE() AS DATE)即可
    AND CAST(d2.update_date AS DATE) = CAST(GETDATE() AS DATE)
)
ORDER BY m.id, d.id

写法2：CTE实现（可读性更强） #

WITH QualifiedMaster AS (
    -- 先拿到所有今日有更新记录的主表ID，去重
    SELECT DISTINCT master_id
    FROM Details
    WHERE CAST(update_date AS DATE) = CAST(GETDATE() AS DATE)
)
SELECT 
    m.id AS master_id,
    d.id AS details_id,
    m.name AS master_name,
    d.info,
    d.update_date
FROM Master m
INNER JOIN QualifiedMaster qm ON m.id = qm.master_id
INNER JOIN Details d ON m.id = d.master_id
ORDER BY m.id, d.id

效果验证 #

两种写法均可适配你提到的两种场景：

1. 单条详情今日更新：返回该主表关联的所有详情记录，符合第一组示例输出
2. 同主表多条详情今日更新：不会返回重复条目，符合第二组示例输出

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