GROUP BY 子句GROUPING SETS、CUBE、ROLLUP
GROUPING 是否聚合，返回值0，1
GROUPING_ID 将该字符串解释为二进制数并返回对应的整数。
GROUPING_ID 将该字符串解释为二进制数并返回对应的整数。
点击(此处)折叠或打开
-
;WITH T AS
-
(
-
SELECT '一中' School, '一年级' Grade, '一班' Class, 30 MCount, 10 FCount UNION ALL
-
SELECT '一中' School, '一年级' Grade, '一班' Class, 20 MCount, 10 FCount
-
)
-
SELECT School, Grade, Class, SUM(MCount) MCount, SUM(FCount) FCount,
-
GROUPING(School) GSchool, GROUPING(Grade) GGrade, GROUPING(Class) GClass,
-
GROUPING_ID(School,Grade,Class) Flag
-
FROM T
-
GROUP BY
-
-
GROUPING SETS
-
(
-
(School,Grade,Class),
-
(School,Grade),
-
(School)
-
)
-
-
--CUBE(School,Grade,Class) 2^N
-
- --ROLLUP(School,Grade,Class) N+1
| SCHOOL | GRADE | CLASS | MCOUNT | FCOUNT | GSCHOOL | GGRADE | GCLASS | FLAG |
|---|---|---|---|---|---|---|---|---|
| 一中 | 一年级 | 一班 | 50 | 20 | 0 | 0 | 0 | 0 |
| 一中 | 一年级 | NULL | 50 | 20 | 0 | 0 | 1 | 1 |
| 一中 | NULL | NULL | 50 | 20 | 0 | 1 | 1 | 3 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26066458/viewspace-2124362/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
启动ASM实例失败ORA-29701
请登录后发表评论
登录
全部评论
