SQL多SELECT语句取交集：如何用INNER JOIN获取4个查询的共同行？

你已经通过INNER JOIN成功关联了两条查询拿到共同用户的结果，要扩展到4条的话，逻辑其实是一致的——把每条查询都封装成子查询，然后依次通过userid做INNER JOIN，毕竟你要找的是在这4个查询结果里都存在的用户。

先看你给出的两条基础查询：

-- 第一条查询：Activity表
SELECT h.userid, 'Activity' as table_name, h.stamp, DATEDIFF(dd, kh.LatestDate, GETDATE()) as days_since, m.group_name 
FROM ([Animal].[SYSADM].[activity_history] h 
INNER JOIN (SELECT userid, MAX(stamp) as LatestDate FROM [Animal].[SYSADM].[activity_history] GROUP BY userid) kh 
    ON h.userid = kh.userid AND h.stamp = kh.LatestDate) 
LEFT OUTER JOIN [Animal].[SYSADM].secure_member m 
    ON m.user_name = h.userid 
WHERE (DATEDIFF(dd, kh.LatestDate, GETDATE()) > 90) AND NOT (m.group_name = 'inactive') 
ORDER BY userid

-- 第二条查询：Person表
SELECT h.userid, 'Person' as table_name, h.stamp, DATEDIFF(dd, kh.LatestDate, GETDATE()) as days_since, m.group_name 
FROM ([Animal].[SYSADM].[person_history] h 
INNER JOIN (SELECT userid, max(stamp) as LatestDate FROM [Animal].[SYSADM].[person_history] GROUP BY userid) kh 
    ON h.userid = kh.userid AND h.stamp = kh.LatestDate) 
LEFT OUTER JOIN [Animal].[SYSADM].secure_member m 
    ON m.user_name = h.userid 
WHERE (DATEDIFF(dd, kh.LatestDate, GetDate()) > 90) AND NOT (m.group_name = 'inactive') 
ORDER BY userid

你已经实现的两条关联代码：

SELECT DISTINCT 
    t1.userid as A_UserID, 
    t2.userid as P_UserID, 
    t1.stamp as A_stamp, 
    t2.stamp as P_stamp, 
    datediff(dd,t1.stamp,GetDate()) as A_days_since, 
    datediff(dd,t2.stamp,GetDate()) as P_days_since, 
    t1.group_name, 
    t1.table_name, 
    t2.table_name 
from (
    SELECT h.userid, 'Activity' as table_name, h.stamp, datediff(dd,kh.LatestDate,GetDate()) as days_since, m.group_name 
    FROM ( [Animal].[SYSADM].[activity_history] h 
    inner join ( select userid, max(stamp) as LatestDate from [Animal].[SYSADM].[activity_history] group by userid ) kh 
        on h.userid = kh.userid and h.stamp = kh.LatestDate ) 
    left outer join [Animal].[SYSADM].secure_member m 
        on m.user_name = h.userid 
    where (datediff(dd,kh.LatestDate, GetDate()) > 90) and not (m.group_name = 'inactive')
) t1 
inner join (
    SELECT h.userid, 'Person' as table_name, h.stamp, datediff(dd,kh.LatestDate,GetDate()) as days_since, m.group_name 
    FROM ( [Animal].[SYSADM].[person_history] h 
    inner join ( select userid, max(stamp) as LatestDate from [Animal].[SYSADM].[person_history] group by userid ) kh 
        on h.userid = kh.userid and h.stamp = kh.LatestDate ) 
    left outer join [Animal].[SYSADM].secure_member m 
        on m.user_name = h.userid 
    where (datediff(dd,kh.LatestDate, GetDate()) > 90) and not (m.group_name = 'inactive')
) t2 on t1.userid = t2.userid 
order by T1.userid

扩展到4条查询的方法 #

假设另外两条查询分别对应X_history和Y_history表（你可以替换成实际的表名），只需要把它们也封装成子查询t3、t4，然后继续通过userid做INNER JOIN即可。完整代码示例如下：

SELECT DISTINCT 
    t1.userid,
    -- 按需选择各子查询的字段，比如：
    t1.stamp as Activity_stamp,
    t2.stamp as Person_stamp,
    t3.stamp as X_stamp,
    t4.stamp as Y_stamp,
    datediff(dd, t1.stamp, GETDATE()) as Activity_days_since,
    datediff(dd, t2.stamp, GETDATE()) as Person_days_since,
    datediff(dd, t3.stamp, GETDATE()) as X_days_since,
    datediff(dd, t4.stamp, GETDATE()) as Y_days_since,
    t1.group_name
from (
    -- 第一条查询：Activity表
    SELECT h.userid, h.stamp, m.group_name 
    FROM ([Animal].[SYSADM].[activity_history] h 
    INNER JOIN (SELECT userid, MAX(stamp) as LatestDate FROM [Animal].[SYSADM].[activity_history] GROUP BY userid) kh 
        ON h.userid = kh.userid AND h.stamp = kh.LatestDate) 
    LEFT OUTER JOIN [Animal].[SYSADM].secure_member m 
        ON m.user_name = h.userid 
    WHERE (DATEDIFF(dd, kh.LatestDate, GETDATE()) > 90) AND NOT (m.group_name = 'inactive')
) t1 
inner join (
    -- 第二条查询：Person表
    SELECT h.userid, h.stamp, m.group_name 
    FROM ([Animal].[SYSADM].[person_history] h 
    INNER JOIN (SELECT userid, max(stamp) as LatestDate FROM [Animal].[SYSADM].[person_history] GROUP BY userid) kh 
        ON h.userid = kh.userid AND h.stamp = kh.LatestDate) 
    LEFT OUTER JOIN [Animal].[SYSADM].secure_member m 
        ON m.user_name = h.userid 
    WHERE (DATEDIFF(dd, kh.LatestDate, GetDate()) > 90) AND NOT (m.group_name = 'inactive')
) t2 on t1.userid = t2.userid
inner join (
    -- 第三条查询：X_history表（替换成你的实际表名）
    SELECT h.userid, h.stamp, m.group_name 
    FROM ([Animal].[SYSADM].[X_history] h 
    INNER JOIN (SELECT userid, max(stamp) as LatestDate FROM [Animal].[SYSADM].[X_history] GROUP BY userid) kh 
        ON h.userid = kh.userid AND h.stamp = kh.LatestDate) 
    LEFT OUTER JOIN [Animal].[SYSADM].secure_member m 
        ON m.user_name = h.userid 
    WHERE (DATEDIFF(dd, kh.LatestDate, GetDate()) > 90) AND NOT (m.group_name = 'inactive')
) t3 on t1.userid = t3.userid
inner join (
    -- 第四条查询：Y_history表（替换成你的实际表名）
    SELECT h.userid, h.stamp, m.group_name 
    FROM ([Animal].[SYSADM].[Y_history] h 
    INNER JOIN (SELECT userid, max(stamp) as LatestDate FROM [Animal].[SYSADM].[Y_history] GROUP BY userid) kh 
        ON h.userid = kh.userid AND h.stamp = kh.LatestDate) 
    LEFT OUTER JOIN [Animal].[SYSADM].secure_member m 
        ON m.user_name = h.userid 
    WHERE (DATEDIFF(dd, kh.LatestDate, GetDate()) > 90) AND NOT (m.group_name = 'inactive')
) t4 on t1.userid = t4.userid
order by t1.userid

关于INTERSECT无结果的说明 #

你之前用INTERSECT没返回结果，大概率是因为INTERSECT要求所有返回字段的值完全匹配才会保留行。而你的每个查询里table_name是固定的不同字符串（比如'Activity'和'Person'），还有stamp、days_since也可能不一样，所以INTERSECT找不到完全匹配的行。而INNER JOIN只基于userid关联，只要用户在所有查询里都存在，就会返回该行的所有相关字段，这正是你需要的结果。

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