SQL Server如何通过公共UserId关联合并三个独立SQL查询输出

可以实现，你可通过公共用户ID关联三个统计结果，同时处理无对应积分时的空值问题，最终输出你需要的四列结果。

实现逻辑 #

1. 分别将三个原始统计查询封装为独立的CTE（公共表表达式），统一用户ID字段别名
2. 汇总三个查询中所有出现过的用户ID，避免遗漏仅在单个表中存在的用户
3. 通过左关联将三类积分匹配到对应用户，用ISNULL函数将无记录的积分值置为0，避免返回空值

完整实现代码 #

-- 封装考勤积分统计
WITH AttendanceStats AS (
    SELECT COUNT(sa.SessionId) AS AttendancePoints, sa.UserId 
    FROM SessionAttendance sa 
    WHERE IsParticipated = 1 
    GROUP BY UserId
),
-- 封装反馈积分统计
FeedbackStats AS (
    SELECT COUNT(sf.Session_Id) AS FeedbackPoints, sf.User_Id AS UserId
    FROM SessionFeedbacks sf 
    GROUP BY User_Id
),
-- 封装活动积分统计
ActivityStats AS (
    SELECT SUM(sac.Points) AS ActivityPoints, sac.UserId 
    FROM StudentActivities sac 
    GROUP BY UserId
),
-- 汇总所有涉及的用户ID，避免遗漏
AllUserIds AS (
    SELECT UserId FROM AttendanceStats
    UNION
    SELECT UserId FROM FeedbackStats
    UNION
    SELECT UserId FROM ActivityStats
)
-- 关联输出最终结果
SELECT 
    au.UserId AS [User Id],
    ISNULL(a.AttendancePoints, 0) AS AttendancePoints,
    ISNULL(f.FeedbackPoints, 0) AS FeedbackPoints,
    ISNULL(ac.ActivityPoints, 0) AS ActivityPoints
FROM AllUserIds au
LEFT JOIN AttendanceStats a ON au.UserId = a.UserId
LEFT JOIN FeedbackStats f ON au.UserId = f.UserId
LEFT JOIN ActivityStats ac ON au.UserId = ac.UserId

补充说明 #

• 如果你现有独立的用户主表，可直接将AllUserIds部分替换为从用户主表取有效用户ID的逻辑，查询效率更高
• 如果你不需要统计仅在单/两个表中存在的用户，可将左关联替换为内关联，仅返回三类积分都存在的用户记录

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