SQL单独的表连接

Question

SELECT COUNT(sa.SessionId) AS AttendancePoints, sa.UserId 
FROM SessionAttendance sa 
WHERE IsParticipated = 1 
GROUP BY UserId
SELECT COUNT(sf.Session_Id) AS FeedbackPoints, sf.User_Id 
FROM SessionFeedbacks sf 
GROUP BY User_Id
SELECT SUM(sac.Points) AS ActivityPoints, sac.UserId 
FROM StudentActivities sac 
GROUP BY UserId

的示例输出是否有办法把这3个输出到单个输出使用一个共同的用户id在SQL Server?

所需输出:

User Id | AttendancePoints | FeedbackPoints | ActivityPoints

Answer 1

在子查询中弹出它们，例如

SELECT AttendancePoints, UserId
FROM (
SELECT COUNT(sa.SessionId) AS AttendancePoints, sa.UserId 
FROM SessionAttendance sa 
WHERE IsParticipated = 1 
GROUP BY UserId
UNION ALL
SELECT COUNT(sf.Session_Id) AS FeedbackPoints, sf.User_Id 
FROM SessionFeedbacks sf 
GROUP BY User_Id
UNION ALL
SELECT SUM(sac.Points) AS ActivityPoints, sac.UserId 
FROM StudentActivities sac 
GROUP BY UserId
) X

Answer 2

with BeginCTE as(
SELECT COUNT(sa.SessionId) AS AttendancePoints, sa.UserId 
FROM SessionAttendance sa 
WHERE IsParticipated = 1 
GROUP BY UserId
),
MiddleCTE as
(
SELECT COUNT(sf.Session_Id) AS FeedbackPoints, sf.UserId 
FROM SessionFeedbacks sf 
GROUP BY UserId
),
EndCTE as
(
SELECT SUM(sac.Points) AS ActivityPoints, sac.UserId 
FROM StudentActivities sac 
GROUP BY UserId
)
select a.UserID,a.AttendancePoints,b.FeedbackPoints,c.ActivityPoints from 
BeginCTE a
INNER JOIN MiddleCTE b ON a.UserId=b.UserId
INNER JOIN EndCTE c ON b.UserId=c.UserId

Answer 3

• 您可以使用union将它们全部合并为1
• 要保持差异化，可以创建类别

with combining_points as (
SELECT COUNT(sa.SessionId) AS AttendancePoints, sa.UserId, 'AttendancePoints' as category
FROM SessionAttendance sa WHERE IsParticipated = 1 GROUP BY UserId
union all
SELECT COUNT(sf.Session_Id) AS FeedbackPoints, sf.User_Id , 'FeedbackScore' as category
FROM SessionFeedbacks sf GROUP BY User_Id
union all
SELECT SUM(sac.Points) AS ActivityPoints, sac.UserId, 'ActivityPoints' as category
FROM StudentActivities sac GROUP BY UserId
)
select * from combining_points