Поиск…


Простая группировка

Таблица заказов

Пользовательский ИД Код товара Количество Цена
1 2 5 100
1 3 2 200
1 4 1 500
2 1 4 50
3 5 6 700

При группировке по определенному столбцу возвращаются только уникальные значения этого столбца.

SELECT customerId
FROM orders
GROUP BY customerId;

Возвращаемое значение:

Пользовательский ИД
1
2
3

Совокупные функции, такие как count() применяются к каждой группе, а не к полной таблице:

SELECT customerId, 
       COUNT(productId) as numberOfProducts,
       sum(price) as totalPrice
FROM orders
GROUP BY customerId;

Возвращаемое значение:

Пользовательский ИД numberOfProducts Итоговая цена
1 3 800
2 1 50
3 1 700

ГРУППА несколькими столбцами

Можно захотеть GROUP BY более чем одним столбцом

declare @temp table(age int, name varchar(15))

insert into @temp
select 18, 'matt' union all
select 21, 'matt' union all
select 21, 'matt' union all
select 18, 'luke' union all
select 18, 'luke' union all
select 21, 'luke' union all
select 18, 'luke' union all
select 21, 'luke'

SELECT Age, Name, count(1) count
FROM @temp 
GROUP BY Age, Name

будут группироваться по возрасту и имени и будут производить:

Возраст название подсчитывать
18 Люк 3
21 Люк 2
18 матовый 1
21 матовый 2

Группировать с помощью нескольких таблиц, несколько столбцов

Группа часто используется с заявлением о соединении. Предположим, у нас есть две таблицы. Первая - таблица студентов:

Я бы ФИО Возраст
1 Мэтт Джонс 20
2 Frank Blue 21
3 Энтони Ангел 18

Вторая таблица - это таблица темы, которую может принять каждый студент:

subject_id Предмет
1 математика
2 PE
3 физика

И потому, что один студент может посещать множество предметов, и один предмет может посещать многие ученики (поэтому отношение N: N), нам нужно иметь третью «ограничительную» таблицу. Назовем таблицу Students_subjects:

subject_id Студенческий билет
1 1
2 2
2 1
3 2
1 3
1 1

Теперь позвольте сказать, что мы хотим знать количество предметов, которые посещают каждый ученик. Здесь автономный оператор GROUP BY недостаточен, поскольку информация недоступна через одну таблицу. Поэтому мы должны использовать GROUP BY с оператором JOIN :

Select Students.FullName, COUNT(Subject Id) as SubjectNumber FROM Students_Subjects
LEFT JOIN Students
ON Students_Subjects.Student_id = Students.Id
GROUP BY Students.FullName

Результат данного запроса выглядит следующим образом:

ФИО SubjectNumber
Мэтт Джонс 3
Frank Blue 2
Энтони Ангел 1

Для еще более сложного примера использования GROUP BY, скажем, студент может присвоить одно и то же имя своему имени более одного раза (как показано в таблице Students_Subjects). В этом случае мы могли бы подсчитать количество раз, когда каждый предмет был назначен ученику по GROUPing более чем на один столбец:

SELECT Students.FullName, Subjects.Subject,
COUNT(Students_subjects.Subject_id) AS NumberOfOrders
FROM ((Students_Subjects
INNER JOIN Students
ON Students_Subjcets.Student_id=Students.Id)
INNER JOIN Subjects
ON Students_Subjects.Subject_id=Subjects.Subject_id)
GROUP BY Fullname,Subject

Этот запрос дает следующий результат:

ФИО Предмет SubjectNumber
Мэтт Джонс математика 2
Мэтт Джонс PE 1
Frank Blue PE 1
Frank Blue физика 1
Энтони Ангел математика 1

HAVING

Поскольку WHERE оценивается до GROUP BY , вы не можете использовать WHERE для оценки результатов группировки (как правило, агрегированную функцию, такую ​​как COUNT(*) ). Чтобы удовлетворить эту потребность, можно использовать предложение HAVING .

Например, используя следующие данные:

DECLARE @orders TABLE(OrderID INT, Name NVARCHAR(100))

INSERT INTO @orders VALUES
( 1, 'Matt' ),
( 2, 'John' ),
( 3, 'Matt' ),
( 4, 'Luke' ),
( 5, 'John' ),
( 6, 'Luke' ),
( 7, 'John' ),
( 8, 'John' ),
( 9, 'Luke' ),
( 10, 'John' ),
( 11, 'Luke' )

Если мы хотим получить количество заказов, которые разместили каждый человек, мы будем использовать

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name

и получить

название заказы
Matt 2
Джон 5
Люк 4

Однако, если мы хотим ограничить это для лиц, которые разместили более двух заказов, мы можем добавить предложение HAVING .

SELECT Name, COUNT(*) AS 'Orders'
FROM @orders
GROUP BY Name
HAVING COUNT(*) > 2

даст

название заказы
Джон 5
Люк 4

Обратите внимание, что, подобно GROUP BY , столбцы, помещенные в HAVING должны точно соответствовать их аналогам в SELECT . Если бы в приведенном выше примере мы сказали, что

SELECT Name, COUNT(DISTINCT OrderID)

наше предложение HAVING должно было бы сказать

HAVING COUNT(DISTINCT OrderID) > 2

GROUP BY с ROLLUP и CUBE

Оператор ROLLUP полезен при создании отчетов, содержащих промежуточные итоги и итоговые значения.

  • CUBE создает набор результатов, который показывает агрегаты для всех комбинаций значений в выбранных столбцах.

  • ROLLUP создает набор результатов, который показывает агрегаты для иерархии значений в выбранных столбцах.

    Вещь цвет Количество
    Таблица синий 124
    Таблица красный 223
    кресло синий 101
    кресло красный 210
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00 

(Затронуто 7 строк)

Если ключевое слово ROLLUP в запросе будет изменено на CUBE, набор результатов CUBE будет таким же, за исключением того, что в конце будут возвращены эти две дополнительные строки:

ALL                  Blue                 225.00                     
ALL                  Red                  433.00 

https://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx



Modified text is an extract of the original Stack Overflow Documentation
Лицензировано согласно CC BY-SA 3.0
Не связан с Stack Overflow