объединение строк в группу по функции с другими агрегатными функциями

Можно ли объединить строки с одной или несколькими другими группами по функциям, таким как сумма, среднее значение, количество и т. д.

Скажем, у меня есть следующая таблица

Id Name Order Value  
1  a    1     100  
2  b    2     200  
3  c    1     300  
4  d    1     100  
5  e    2     300

Теперь, если я хочу, чтобы результат был чем-то вроде этого

Order Name   Value Count  
1     a,c,d  500   3  
2     b,e    500   2

Как я могу добиться того же, используя запрос на сервере SQL.


sql sql-server tsql aggregate-functions sql-server-group-concat
person Sarah    schedule 06.04.2011    source источник

Ответы (3)


arrow_upward
7
arrow_downward

Образец таблицы

create table t123 (Id int, Name varchar(10), [Order] int, Value int)
insert t123 select 
1,'a','1',100 union all select
2,'b','2',200 union all select
3,'c','1',300 union all select
4,'d','1',100 union all select
5,'e','2',300

Запрос для SQL Server 2005 и выше

select a.[order], STUFF((
    select ','+b.name
    from t123 b
    where b.[order] = a.[order]
    order by b.name
    for xml path('a'), type).value('.','nvarchar(max)'),1,1,'') Name,
    SUM(a.value) value,
    COUNT(*) [count]
from t123 a
group by a.[order]

Вывод

order       Name         value       count
----------- ------------ ----------- -----------
1           a,c,d        500         3
2           b,e          500         2
person RichardTheKiwi    schedule 06.04.2011
comment
Не могли бы вы объяснить, что, черт возьми, здесь происходит ... моя таблица не настоящая таблица, это результат объединения нескольких таблиц. - person Sarah; 07.04.2011

arrow_upward
4
arrow_downward

Попробуйте использовать это.

  • Я поместил ваш исходный запрос в CTE.
  • Затем я выбрал из него и сгруппировал по порядку.
  • Затем я применяю перекрестный запрос с подзапросом, который получает набор имен с разделителями-запятыми для каждого заказа во внешнем запросе.
  • Поскольку я также выбрал столбец имен, мне также пришлось группировать по столбцу имен.

Нравится:

;WITH cte(id, n, o, v) as (
   SELECT Id, Name, Order, Value FROM ....
)
SELECT o, names, SUM(v), COUNT(*)
FROM cte AS outer
CROSS APPLY (
    SELECT Name+',' 
    FROM cte AS inner 
    WHERE outer.o = inner.o 
    ORDER BY Name FOR XML PATH('')
) n(names)
group by o, names
person Jeff Meatball Yang    schedule 08.04.2011

arrow_upward
1
arrow_downward

Если вы используете MS SQL Server 2005 или более позднюю версию, вы можете создавать пользовательские агрегатные функции.

MSDN: CREATE AGGREGATE (Transact-SQL)

MSDN: вызов пользовательских агрегатных функций CLR

person BodhiD    schedule 08.04.2011