点击(此处)折叠或打开

WITH T AS
(
SELECT 1 ID, '张三' NAME UNION ALL
SELECT 2, '李四' UNION ALL
SELECT 3, '王五'
),
T2 AS
(
SELECT 1 XID, 1 ID, '语文' PROJ, 90 SCORE UNION ALL
SELECT 2, 1, '数学', 80 UNION ALL
SELECT 3, 2, '语文', 80 UNION ALL
SELECT 4, 2, '数学', 70 UNION ALL
SELECT 5, 2, '历史', 70
),
T3 AS
(
SELECT T.ID, T.NAME, T2.PROJ, T2.SCORE FROM T OUTER APPLY (SELECT * FROM T2 WHERE T2.ID=T.ID) T2
)
SELECT ID,NAME,(STUFF((SELECT ','+T.PROJ FROM T3 T WHERE T.ID=T3.ID FOR XML PATH('')),1,1,'')) P FROM T3 GROUP BY ID,NAME
/*
SELECT * FROM T CROSS APPLY T2 WHERE T.ID=T2.ID --CROSS APPLY 相当 INNER JOIN
SELECT TOP 1 WITH TIES * FROM T2 ORDER BY ID --看最后一个如果都一样都算上
*/

ITPub博客

CROSS APPLY 和 OUTER APPLY