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
打印页面
上一页
下一页
广告