如何在含EXISTS条件的两表SQL查询中整合DATEDIFF计算？

根据你的需求，需要同时满足table1与table2的关联条件、table2的两个筛选条件，并且从table2中计算duration字段，以下是几种可行的实现方式：

方法一：内连接（INNER JOIN） #

直接通过colID关联两张表，同时添加两个筛选条件，一次性获取所有需要的字段。这种方式逻辑直观，适合需要返回table2中所有匹配行的场景。

SELECT 
    a.col1, 
    a.col2, 
    a.col3,
    DATEDIFF(day, b.start_date, GETDATE()) AS duration,
    b.name
FROM table1 a
INNER JOIN table2 b 
    ON b.colID = a.colID
WHERE 
    b.col LIKE 'Sample%'
    AND b.code LIKE 'xxx%'

如果table2中同一个colID对应多行符合条件的记录，查询结果会返回多条重复的table1记录（对应table2的每一行），若需避免重复，可以添加DISTINCT或者使用聚合函数（比如MAX(duration)）。

方法二：SELECT子查询获取计算列 #

在SELECT语句中嵌入子查询，单独获取table2的duration和name字段，同时保留原有的EXISTS条件确保只返回存在匹配记录的table1数据。这种方式适合table2中每个colID仅有一行符合条件的场景。

SELECT 
    a.col1, 
    a.col2, 
    a.col3,
    (SELECT DATEDIFF(day, start_date, GETDATE()) 
     FROM table2 b 
     WHERE b.colID = a.colID 
       AND b.col LIKE 'Sample%'
       AND b.code LIKE 'xxx%') AS duration,
    (SELECT name 
     FROM table2 b 
     WHERE b.colID = a.colID 
       AND b.col LIKE 'Sample%'
       AND b.code LIKE 'xxx%') AS name
FROM table1 a
WHERE EXISTS (
    SELECT * 
    FROM table2 b
    WHERE b.colID = a.colID
      AND b.col LIKE 'Sample%'
      AND b.code LIKE 'xxx%'
)

如果table2中存在多行匹配同一个colID，子查询会报错，此时可以在子查询中添加TOP 1并配合ORDER BY指定取哪一行数据。

方法三：使用OUTER APPLY关联子查询 #

APPLY运算符可以针对table1的每一行执行子查询，灵活获取table2中符合条件的记录，适合需要对子查询结果做排序、筛选特定行的场景。

SELECT 
    a.col1, 
    a.col2, 
    a.col3,
    b.duration,
    b.name
FROM table1 a
OUTER APPLY (
    SELECT 
        DATEDIFF(day, start_date, GETDATE()) AS duration,
        name
    FROM table2 
    WHERE colID = a.colID
      AND col LIKE 'Sample%'
      AND code LIKE 'xxx%'
    -- 若table2有多行匹配，可通过ORDER BY和TOP 1指定取哪一行，例如取最新的start_date记录
    -- ORDER BY start_date DESC
    -- TOP 1
) b
WHERE b.duration IS NOT NULL -- 等同于原查询的EXISTS条件，过滤无匹配的table1记录

如果不需要过滤无匹配的记录，可以去掉最后的WHERE条件，此时无匹配的duration和name会显示为NULL。

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