CROSS APPLY 和 OUTER APPLY

最新推荐文章于 2023-09-19 18:38:00 发布

cuikuang6741

最新推荐文章于 2023-09-19 18:38:00 发布

阅读量74

点赞数

点击(此处)折叠或打开

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 --看最后一个如果都一样都算上
*/