共用方式為


GROUPING_ID (Transact-SQL)

這是計算群組層級的函數。當指定 GROUP BY 時,GROUPING_ID 只能在 SELECT <select> 清單、HAVING 或 ORDER BY 子句中使用。

主題連結圖示Transact-SQL 語法慣例

語法

GROUPING_ID ( <column_expression>[ ,...n ] )

引數

  • <<column_expression>>
    這是 GROUP BY 子句中的 column_expression。

傳回類型

int

備註

GROUPING_ID <column_expression> 必須完全符合 GROUP BY 清單中的運算式。例如,如果您要根據 DATEPART (yyyy, <column name>) 分組,請使用 GROUPING_ID (DATEPART (yyyy, <column name>));如果您要根據 <column name> 分組,請使用 GROUPING_ID (<column name>)。

比較 GROUPING_ID () 與 GROUPING ()

GROUPING_ID (<column_expression> [ ,...n]) ]) 針對每一個輸出資料列中資料行清單中的每一個資料行輸入 GROUPING (<column_expression>) 對等項目傳回 (當做一和零的字串)。GROUPING_ID 會將此字串解譯為以 2 為基底的數字,並傳回對等的整數。例如,假設有以下的陳述式:SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>。下表顯示 GROUPING_ID () 輸入和輸出值。

彙總資料行

GROUPING_ID (a, b, c) 輸入 = GROUPING(a) + GROUPING(b) + GROUPING(c)

GROUPING_ID () 輸出

a

100

4

b

010

2

c

001

1

ab

110

6

ac

101

5

bc

011

3

abc

111

7

GROUPING_ID () 的技術定義

每一個 GROUPING_ID 引數都必須是 GROUP BY 清單中的元素。GROUPING_ID () 會傳回 integer 點陣圖,可能會引發其最低的 N 位元。引發的 bit 指示對應的引數不是給定輸出資料列的群組資料行。最低順位的 bit 會對應到引數 N,而 N-1 最低順位 bit 會對應到引數 1。

GROUPING_ID () 對等項目

如果是單一群組查詢,GROUPING (<column_expression>) 會等於 GROUPING_ID (<column_expression>) 並傳回 0。

例如,下列陳述式是相等的:

SELECT GROUPING_ID(A,B)
FROM T 
GROUP BY CUBE(A,B) 
SELECT 3 FROM T GROUP BY ()
UNION ALL
SELECT 1 FROM T GROUP BY A
UNION ALL
SELECT 2 FROM T GROUP BY B
UNION ALL
SELECT 0 FROM T GROUP BY A,B

範例

A. 使用 GROUPING_ID 識別群組層級

下列範例會依據 Name 和 Title、Name, 和公司總數傳回員工計數。GROUPING_ID() 會用來針對 Title 資料行中識別彙總層級的每一個資料列各建立一個值。

USE AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title);

B. 使用 GROUPING_ID 篩選結果集

簡單範例

在下列程式碼中,若只要依據職稱傳回具有員工計數的資料列,請從 HAVING GROUPING_ID(D.Name, E.Title); = 0 中移除註解字元。若只要依據部門傳回具有員工計數的資料列,請從 HAVING GROUPING_ID(D.Name, E.Title) = 1; 中移除註解字元。

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name

以下是未篩選的結果集。

Name

Title

Grouping Level

Employee Count

Name

Document Control

Control Specialist

0

2

Document Control

Document Control

Document Control Assistant

0

2

Document Control

Document Control

Document Control Manager

0

1

Document Control

Document Control

NULL

1

5

Document Control

Facilities and Maintenance

Facilities Administrative Assistant

0

1

Facilities and Maintenance

Facilities and Maintenance

Facilities Manager

0

1

Facilities and Maintenance

Facilities and Maintenance

Janitor

0

4

Facilities and Maintenance

Facilities and Maintenance

Maintenance Supervisor

0

1

Facilities and Maintenance

Facilities and Maintenance

NULL

1

7

Facilities and Maintenance

NULL

NULL

3

12

NULL

複雜範例

在下列範例中,GROUPING_ID() 是用來篩選包含多個群組層級的結果集 (藉由分組層級)。類似的程式碼可用來建立有數個群組層級的檢視以及可呼叫此檢視的預存程序,其方式是傳遞藉由分組層級篩選檢視的參數。

USE AdventureWorks;
GO
DECLARE @Grouping nvarchar(50);
DECLARE @GroupingLevel smallint;
SET @Grouping = N'CountryRegionCode Total';

SELECT @GroupingLevel = (
    CASE @Grouping
        WHEN N'Grand Total'             THEN 15
        WHEN N'SalesPerson Total'       THEN 14
        WHEN N'Store Total'             THEN 13
        WHEN N'Store SalesPerson Total' THEN 12
        WHEN N'CountryRegionCode Total' THEN 11
        WHEN N'Group Total'             THEN 7
        ELSE N'Unknown'
    END);

SELECT 
    T.[Group]
    ,T.CountryRegionCode
    ,S.Name AS N'Store'
    ,(SELECT C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = H.SalesPersonID)
        AS N'Sales Person'
    ,SUM(TotalDue)AS N'TotalSold'
    ,CAST(GROUPING(T.[Group])AS char(1)) + 
        CAST(GROUPING(T.CountryRegionCode)AS char(1)) + 
        CAST(GROUPING(S.Name)AS char(1)) + 
        CAST(GROUPING(H.SalesPersonID)AS char(1)) 
        AS N'GROUPING base-2'
    ,GROUPING_ID((T.[Group])
        ,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
        ) AS N'GROUPING_ID'
    ,CASE 
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 15 THEN N'Grand Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 14 THEN N'SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 13 THEN N'Store Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 12 THEN N'Store SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 11 THEN N'CountryRegionCode Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) =  7 THEN N'Group Total'
        ELSE N'Error'
        END AS N'Level'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
    ,(H.SalesPersonID),(S.Name)
    ,(T.[Group]),(T.CountryRegionCode),()
    )
HAVING GROUPING_ID(
    (T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
    ) = @GroupingLevel
ORDER BY 
    GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
    ,(T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID))ASC;

C. 搭配 ROLLUP 和 CUBE 使用 GROUPING_ID () 來識別群組層級

下列範例的程式碼會示範如何使用 GROUPING() 來計算 Bit Vector(base-2) 資料行。GROUPING_ID() 是用來計算對應的 Integer Equivalent 資料行。GROUPING_ID() 函數中的資料行順序與 GROUPING() 函數串連之資料行的資料行順序相反。

在這些範例中,GROUPING_ID() 是用來針對 Grouping Level 資料行中的每一個資料列各建立一個值,以識別群組的層級。群組層級不一定是以 (0, 1, 2,...n) 開頭之整數的連續清單。

[!附註]

GROUPING 和 GROUPING_ID 可用於 HAVING 子句來篩選結果集。

ROLLUP 範例

在此範例中,所有的群組層級不會與下列 CUBE 範例一樣出現。如果 ROLLUP 清單中的資料行順序改變,Grouping Level 資料行中的層級值也必須改變。

USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
     AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY ROLLUP(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm,OrderDate)
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

以下為部分結果集。

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2003

1

1

1762381

000

0

Year Month Day

2003

1

2

21772.35

000

0

Year Month Day

2003

2

1

3185233

000

0

Year Month Day

2003

2

2

21684.41

000

0

Year Month Day

2004

1

1

2239208

000

0

Year Month Day

2004

1

2

46458.07

000

0

Year Month Day

2004

2

1

3653194

000

0

Year Month Day

2004

2

2

54598.55

000

0

Year Month Day

2003

1

NULL

1784153

100

1

Year Month

2003

2

NULL

3206917

100

1

Year Month

2004

1

NULL

2285666

100

1

Year Month

2004

2

NULL

3707793

100

1

Year Month

2003

NULL

NULL

4991070

110

3

Year

2004

NULL

NULL

5993459

110

3

Year

NULL

NULL

NULL

10984529

111

7

Grand Total

CUBE 範例

在此範例中,GROUPING_ID() 函數是用來針對 Grouping Level 資料行中的每一個資料列各建立一個值,以識別群組的層級。

與之前範例中的 ROLLUP 不同,CUBE 會輸出所有群組層級。如果 CUBE 清單中的資料行順序改變,Grouping Level 資料行中的層級值也必須改變。

USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
        AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'Year Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY CUBE(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

以下為部分結果集。

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2003

1

1

1762381

000

0

Year Month Day

2003

1

2

21772.35

000

0

Year Month Day

2003

2

1

3185233

000

0

Year Month Day

2003

2

2

21684.41

000

0

Year Month Day

2004

1

1

2239208

000

0

Year Month Day

2004

1

2

46458.07

000

0

Year Month Day

2004

2

1

3653194

000

0

Year Month Day

2004

2

2

54598.55

000

0

Year Month Day

2003

1

NULL

1784153

100

1

Year Month

2003

2

NULL

3206917

100

1

Year Month

2004

1

NULL

2285666

100

1

Year Month

2004

2

NULL

3707793

100

1

Year Month

2003

NULL

1

4947613

010

2

Year Day

2003

NULL

2

43456.76

010

2

Year Day

2004

NULL

1

5892402

010

2

Year Day

2004

NULL

2

101056.6

010

2

Year Day

2003

NULL

NULL

4991070

110

3

Year

2004

NULL

NULL

5993459

110

3

Year

NULL

1

1

4001589

001

4

Month Day

NULL

1

2

68230.42

001

4

Month Day

NULL

2

1

6838427

001

4

Month Day

NULL

2

2

76282.96

001

4

Month Day

NULL

1

NULL

4069819

101

5

Month

NULL

2

NULL

6914710

101

5

Month

NULL

NULL

1

10840016

011

6

Day

NULL

NULL

2

144513.4

011

6

Day

NULL

NULL

NULL

10984529

111

7

Grand Total