我有一张表，例如：
|Date |Name|
--------------------
|'20-May-2011'|Bob |
|'20-May-2011'|Fred|
|'20-May-2011'|Jim |
|'21-May-2011'|Bob |
|'21-May-2011'|Ed |
|'22-May-2011'|Bill|
我需要一个查询来返回：
|Date |Count|Names |
--------------------------------------
|'20-May-2011'| 3|'Bob, Fred, Jim'|
|'21-May-2011'| 2|'Bob, Ed' |
|'22-May-2011'| 1|'Bill' |
换句话说，我想要一个列表和按日期排列的姓名计数。
我能想到的最好的办法是：
SELECT list.[Date], [Count], [Names]
FROM (
SELECT [Date],
STUFF((
SELECT ', ' + [Name]
FROM #table t2
WHERE t2.[Date] = t.[Date]
ORDER BY [Name]
FOR XML PATH('')
), 1, 2, '') AS [Names]
FROM #table t
GROUP BY [Date]
) [list]
INNER JOIN (
SELECT [Date],
COUNT(*) AS [Count]
FROM #table t
GROUP BY [Date]
) [count]
ON list.[Date] = count.[Date]
ORDER BY [Count] DESC, list.[Date]
有更优雅的查询吗？
SELECT [Date],
COUNT(*) AS [Count],
STUFF((
SELECT ', ' + [Name]
FROM #table t2
WHERE t2.[Date] = t.[Date]
ORDER BY [Name]
FOR XML PATH('')
), 1, 2, '') AS [Names]
FROM #table t
GROUP BY [Date]
如果您认为名称列可能包含<>'"&你应该这样做：
SELECT [Date],
COUNT(*) AS [Count],
STUFF((
SELECT ', ' + [Name]
FROM #table t2
WHERE t2.[Date] = t.[Date]
ORDER BY [Name]
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)'), 1, 2, '') AS [Names]
FROM #table t
GROUP BY [Date]
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
