復(fù)制代碼 代碼如下:

SELECT * FROM (SELECT DISTINCT Name,Count(ID) as 人員個(gè)數(shù),
sum(case when Sex='男' then 1 else 0 end) as 男人數(shù),
sum(case when Sex='女' then 1 else 0 end) as 女人數(shù)
FROM Test group by Name) A
OUTER APPLY(
SELECT
[包含ID]= STUFF(REPLACE(REPLACE(
(
SELECT ID FROM Test N
WHERE Name = A.Name
FOR XML AUTO
), '<N/>', ''), 1, 1, '')
)N