Microsoft SQL Server
ГРУППА ПО
Поиск…
Простая группировка
Таблица заказов
Пользовательский ИД | Код товара | Количество | Цена |
---|---|---|---|
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