有很多时候需要将同组数据合并串起来显示,可以利用 FOR XML PATH('')
例如:
CREATE TABLE #student (studentName varchar(50),className varchar(50))
INSERT INTO #student
SELECT '小明','英语' union
SELECT '小明','数学' union
SELECT '小明','语文' union
SELECT '小明','历史' union
SELECT '小强','地理' union
SELECT '小强','政治' union
SELECT '小强','数学'
SELECT * FROM #student
SELECT studentName, ( select className + ','
from #student
where studentName = a.studentName
FOR XML PATH('')
)
,count(className) counts
FROM #student a
group by studentName
order by count(className) desc