SQL FOR XML PATH列表和COUNT

问题描述

我有一张桌子，例如:

|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'          |

换句话说，我想要按日期列出名称的列表和数量.我能想到的最好的是:

In other words, I want a list and a count of the names by date.The best I can come up with is:

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]

如果您认为名称"列可能包含<>'"&，则应这样做:

If you think that the Name column might contain <>'"& you should do like this instead:

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]

这篇关于SQL FOR XML PATH列表和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！