SQL - GROUPING_ID() 函式

上一頁
下一頁

SQL 的Grouping_ID()函式用於計算分組級別。它只能與SELECT語句、HAVING子句或ORDERED BY子句一起使用，前提是已指定GROUP BY。

GROUPING_ID()函式返回一個整數點陣圖，其中最低N位被點亮。點亮的位表示相應的引數不是指定輸出行的分組列。引數N由最低位表示，即0，引數1由第N-1個最低位表示。

其中，0指定屬於分組的元素，1指定不屬於分組集的元素。

語法

以下是SQL GROUPING_ID()函式的語法：

SELECT GROUPING_ID([column1, column2, ..., columnN])
FROM table_name/source
GROUP BY column1, column2, ..., columnN ;

GROUPING_ID列表達式必須與GROUP BY列表中的表示式完全匹配。

示例

在下面的示例中，我們演示了GROUPING_ID()函式，並顯示了來自customers表的分組級別。首先，讓我們使用以下查詢建立一個CUSTOMERS表：

CREATE TABLE customers(ID INT NOT NULL, 
NAME VARCHAR(30) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR(30), 
SALARY DECIMAL(18, 2));

該表儲存ID、NAME、AGE、ADDRESS和SALARY。現在，我們使用INSERT語句在customers表中插入7條記錄。

INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00);
INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null);
customers表如下所示：
+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
|  7 | Aman     |  23 | Ranchi    |    NULL |
+----+----------+-----+-----------+---------+

以下SQL查詢顯示客戶的ID和AGE以及分組級別：

SELECT
   ID, AGE, GROUPING_ID(ID, AGE) AS Level
   FROM customers
   GROUP BY CUBE(ID, AGE);

輸出

以下是上述SQL查詢的輸出：

+------+------+--------+
|   ID |  AGE |  Level |
+------+------+--------+
|    6 |   22 |      0 |
| NULL |   22 |      2 |
|    3 |   23 |      0 |
|    7 |   23 |      0 |
| NULL |   23 |      2 |
|    2 |   25 |      0 |
|    4 |   25 |      0 |
| NULL |   25 |      2 |
|    5 |   27 |      0 |
| NULL |   27 |      2 |
|    1 |   32 |      0 |
| NULL |   32 |      2 |
| NULL | NULL |      3 |
|    1 | NULL |      1 |
|    2 | NULL |      1 |
|    3 | NULL |      1 |
|    4 | NULL |      1 |
|    5 | NULL |      1 |
|    6 | NULL |      1 |
|    7 | NULL |      1 |
+------+------+--------+

示例

在下面的示例中，我們使用GROUPING_ID()函式顯示兩列的分組級別。使用CREATE語句，我們建立了另一個名為EMP_Details的表：

CREATE TABLE EMP_Details
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   FIRTSNAME VARCHAR(100) ,
   LASTNAME VARCHAR(100),
   LOCATION VARCHAR(100),
   DOB DATETIME,
   SALARY MONEY,
   DEPT INT
)

該表儲存ID、firstname、lastname、location、dob、SALARY和dept。現在，我們使用INSERT語句在EMP_Details表中插入8條記錄。

INSERT INTO EMP_Details VALUES
   ('AKASH','KALLURI','HYDERABAD','07-23-1989',24000,1),
   ('GANESH','CH','PUNE','07-23-1987',48000,1),
   ('RAHUL','KUMAR','HYDERABAD','07-23-1988',25000,1),
   ('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),
   ('SURAJ','MD','HYDERABAD','07-23-1987',38000,2),
   ('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),
   ('VIVEK','KUMAR','CHENNAI','03-23-1986',47000,1),
   ('AMAN','KUMAR','DELHI','07-23-1988',33000,2);
以下是EMP_Details表：
+----+-----------+----------+-----------+-------------------------+----------+------+
| ID | FIRTSNAME | LASTNAME | LOCATION  |                    DOB  |   SALARY | DEPT |
+----+-----------+----------+-----------+-------------------------+----------+------+
|  1 |     AKASH |  KALLURI | HYDERABAD | 1989-07-23 00:00:00.000 | 24000.00 |    1 |
|  2 |    GANESH |       CH |      PUNE | 1987-07-23 00:00:00.000 | 48000.00 |    1 |
|  3 |     RAHUL |    KUMAR | HYDERABAD | 1988-07-23 00:00:00.000 | 25000.00 |    1 |
|  4 | VENKATESH |BODUPPALY | HYDERABAD | 1986-07-23 00:00:00.000 | 32000.00 |    2 |
|  5 |     SURAJ |       MD | HYDERABAD | 1987-07-23 00:00:00.000 | 38000.00 |    2 |
|  6 |     GANGA |  RAJAYAM |      PUNE | 1987-05-26 00:00:00.000 |390000.00 |    2 |
|  7 |     VIVEK |    KUMAR |   CHENNAI | 1986-03-23 00:00:00.000 | 47000.00 |    1 |
|  8 |      AMAN |    KUMAR |     DELHI | 1988-07-23 00:00:00.000 | 33000.00 |    2 |
+----+-----------+----------+-----------+-------------------------+----------+------+

以下SQL查詢顯示EMP_Details的DEPT、YEAR、GRP_Level和COUNT：

SELECT
DEPT,YEAR(DOB) AS YEAR, 
GROUPING_ID(DEPT,YEAR(DOB)) GRP_LEVEL, COUNT(*) AS COUNT
FROM EMP_Details
GROUP BY ROLLUP((DEPT),(YEAR(DOB)),LOCATION);

輸出

+--------+-------+------------+--------+
|   DEPT |  YEAR |  GRP_Level |  COUNT |
+--------+-------+------------+--------+
|      1 |  1986 |          0 |      1 |
|      1 |  1986 |          0 |      1 |
|      1 |  1987 |          0 |      1 |
|      1 |  1987 |          0 |      1 |
|      1 |  1988 |          0 |      1 |
|      1 |  1988 |          0 |      1 |
|      1 |  1989 |          0 |      1 |
|      1 |  1989 |          0 |      1 |
|      1 |  NULL |          1 |      4 |
|      2 |  1986 |          0 |      1 |
|      2 |  1986 |          0 |      1 |
|      2 |  1987 |          0 |      1 |
|      2 |  1987 |          0 |      1 |
|      2 |  1987 |          0 |      2 |
|      2 |  1988 |          0 |      1 |
|      2 |  1988 |          0 |      1 |
|      2 |  NULL |          1 |      4 |
|   NULL |  NULL |          3 |      8 |
+------+------+---------------+--------+

示例

在下面的示例中，我們使用上述EMP_details表中的GROUPING_ID()函式顯示三列的分組級別：

以下SQL查詢顯示姓氏、出生年份、地點和分組級別：

SELECT
LASTNAME,YEAR(DOB) As YEAR, 
LOCATION, GROUPING_ID(LASTNAME, YEAR(DOB), LOCATION) GRP_Level
FROM EMP_Details
GROUP BY ROLLUP((LASTNAME),(YEAR(DOB)),LOCATION);

輸出

以下是上述SQL查詢的輸出：

+------------+-------+-----------+------------+
|   LASTNAME |  YEAR |  LOCATION |  GRP_Level |
+------------+-------+-----------+------------+
|  BODUPPALY |  1989 | HYDERABAD |          0 |
|  BODUPPALY |  1989 |      NULL |          1 |
|  BODUPPALY |  NULL |      NULL |          3 |
|         CH |  1987 |      PUNE |          0 |
|         CH |  1987 |      NULL |          1 |
|         CH |  NULL |      NULL |          3 |
|     KALLURI|  1989 | HYDERABAD |          0 |
|     KALLURI|  1989 |      NULL |          1 |
|    KALLURI |  NULL |      NULL |          3 |
|      KUMAR |  1986 |   CHENNAI |          0 |
|      KUMAR |  1986 |      NULL |          1 |
|      KUMAR |  1988 |     DELHI |          0 |
|      KUMAR |  1988 | HYDERABAD |          0 |
|      KUMAR |  1988 |      NULL |          1 |
|      KUMAR |  NULL |      NULL |          3 |
|         MD |  1987 | HYDERABAD |          0 |
|         MD |  1987 |      NULL |          1 |
|         MD |  NULL |      NULL |          3 |
|    RAJAYAM |  1987 |      PUNE |          0 |
|    RAJAYAM |  1987 |      NULL |          1 |
|    RAJAYAM |  NULL |      NULL |          3 |
|       NULL |  NULL |      NULL |          7 |
+------------+-------+-----------+------------+
sql-aggregate-functions.htm
列印頁面
上一頁
下一頁
廣告