Список 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'          |

Другими словами, мне нужен список и количество имен по дате. Лучшее, что я могу придумать, это:

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]

Есть ли более элегантный запрос?


sql-server for-xml-path
person Tom Hunter    schedule 20.05.2011    source источник
comment
Нет, я не думаю, что в настоящее время существует какой-либо значительно более простой или элегантный код для SQL Server.   -  person marc_s    schedule 20.05.2011
comment
Лоуренс, я не понимаю, что ты имеешь в виду. Это транзакционный SQL, выполняемый непосредственно на сервере.   -  person Tom Hunter    schedule 20.05.2011
comment
Мне в основном интересно, можно ли это сделать в одном запросе, а не в двух подзапросах, объединенных вместе.   -  person Tom Hunter    schedule 20.05.2011

Ответы (2)


arrow_upward
6
arrow_downward 

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]

Если вы считаете, что столбец Name может содержать <>'"&, вам следует сделать следующее:

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]
person Mikael Eriksson    schedule 20.05.2011
comment
Спасибо, Микаэль, это оно. Не уверен, почему я не мог заставить его работать вчера. - person Tom Hunter; 21.05.2011

arrow_upward
2
arrow_downward

Не намного лучше, но, возможно, использование одного CTE для «инкапсуляции» XML-PATH-наполнения в более презентабельный способ сработает?

;WITH ConsolidatedData AS
(
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
)
SELECT
    [Date], Names, COUNT(*)
FROM 
    ConsolidatedData
GROUP BY 
    [Date], Names

Не уверен, что вы считаете это одним «составным» утверждением или двумя.... :-)

Один совет: старайтесь не использовать идентификаторы SQL Server и зарезервированные слова (например, Date или Order) в качестве собственных имен столбцов и/или таблиц... это всегда довольно беспорядочно....

person marc_s    schedule 20.05.2011
comment
Итак, вы возражаете против SELECT [SELECT] FROM [FROM] WHERE [WHERE] = 'WHERE'? - person Yuck; 20.05.2011
comment
@Yuck: ОТВРАТ!! :-) Да, это кажется немного неприятным :-) - person marc_s; 20.05.2011
comment
@Yuck - Можно и это добавить GROUP BY [GROUP BY] ORDER BY [ORDER BY] - person Brent D; 20.05.2011