使用 GROUPING SETS 的 SQL 查詢以使用 HAVING 子句獲取小計

假設我有一個（虛構的）表格City，其中包含以下列：

Country: string  
State: string 
City: string 
Population: integer

我想要一個查詢，按州、國家和整體顯示總人口，不包括低於 1,000,000 居民的州。

SELECT Country, State, SUM(Population) AS [Total]
FROM   City
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )     
HAVING SUM(Population) < 1000000
ORDER  BY Country, State

此查詢的問題在於，Country和Overall總數將包括低於 1,000,000 居民的州的人口（意味著州的總數與其母國的總數不匹配）。（這是假設這些國家有超過 1,000,000 名居民）

我們如何修改上面的查詢以滿足要求？

我正在使用sql azure …

您必須在分組之前應用“有條款”

SELECT Country, State, SUM(Population) AS [Total]
FROM
(
   SELECT Country, State, SUM(Population) AS [Population]
   FROM   City
   GROUP  BY Country, State  
   HAVING SUM(Population) > 1000000
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () ) 
--HAVING SUM(Population) > 1000000
ORDER BY Country, State

這是一個範例：

;with City
AS
(SELECT 'Count1' as Country,'St1' as State,'C1' as City,100 As Population Union ALL
SELECT 'Count1', 'St1' , 'C2', 50 Union ALL
SELECT 'Count1','St2' , 'CCC 1', 50 Union ALL
SELECT 'Count1','St2' , 'CCC 2 ', 40 
)


SELECT Country, State, SUM(Population) AS [Total]
FROM
(
   SELECT Country, State, SUM(Population) AS [Population]
   FROM   City
   GROUP  BY Country, State  
   HAVING SUM(Population) > 100
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () ) 
--HAVING SUM(Population) > 100
ORDER BY Country, State

輸出：

Country State   Total
               150
Count1          150
Count1  St1     150

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/221059