ROLLUP - tworzy podsumowania w hierarchii grup kolumn w podanej kolejności.
CUBE - tworzy podsumowania dla każdej możliwej kombinacji kolumn. (kombinacji kolumn jest więcej niż w ROLLUP)
--1 GROUP BY ROLLUP (wiele kolumn)
SELECT
ISNULL(cr.[Name],'_ALL') as Country,
ISNULL(s.[Name],'_ALL') as CountryState,
count(a.AddressID) as CountAddressInState
FROM
[Person].[Address] as a
join Person.StateProvince as s
on s.StateProvinceID = a.StateProvinceID
join Person.CountryRegion as cr
on cr.CountryRegionCode = s.CountryRegionCode and
cr.[Name] in ('Canada','Germany') and
s.[Name] in ('Brandenburg','Bayern','Alberta','Quebec')
GROUP BY
ROLLUP(cr.[Name], s.[Name])
ORDER BY
1 desc,
2 desc
--2 GROUP BY CUBE (wiele kolumn)
SELECT
ISNULL(cr.[Name],'_ALL') as Country,
ISNULL(s.[Name],'_ALL') as CountryState,
count(a.AddressID) as CountAddressInState
FROM
[Person].[Address] as a
join Person.StateProvince as s
on s.StateProvinceID = a.StateProvinceID
join Person.CountryRegion as cr
on cr.CountryRegionCode = s.CountryRegionCode and
cr.[Name] in ('Canada','Germany') and
s.[Name] in ('Brandenburg','Bayern','Alberta','Quebec')
GROUP BY
CUBE(cr.[Name], s.[Name])
ORDER BY
1 desc,
2 desc
 |
| Rollup vs Cube |