极客教程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、姓名、年龄、地址和工资。 现在我们正在使用INSERT语句将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);
+----+----------+-----+-----------+---------+
| 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和年龄，以及分组级别-
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、名字、姓氏、位置、出生日期、薪水和部门。现在我们使用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);
+----+-----------+----------+-----------+-------------------------+----------+------+
| 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 |
+------------+-------+-----------+------------+