GROUP BY,HAVING ,With ROLLUP, With CUBE组合查询好总结

一、GROUP
BY的理解

 

GROUP
BY是SELECT语句之起句,用来指定询问分组条件,主要为此来对查询的结果进行分组,相同组合的分组条件在结果集中只显示一行记录。使用GROUP
BY从句时候,通过添加聚合函数(主要有COUNT()、SUM、MAX()、MIN()等)可以使数据聚合。

GROUP BY插叙列中采取聚合函数是对准每个分组的。例如:

 

SELECT SUBSTR(A.HYLB_DM,1,2),COUNT(*)

FROM DJ_ZT A

GROUP BY SUBSTR(A.HYLB_DM,1,2);


01      2071

02      679

03      17952

04      150

05      5921

06      11406

07      3030

08      51308

09      3940

10      1229

11      3548

12      6916

13      1003

14      537

15      11471

16      44

17      135

18      676

19      5747

‘[Null]’        84

 

GROUP
BY用来指定分组条件,是一个数学集合的概念,比如针对同排进行分组,则构成分组条件的集合数为1。如果来点儿独分组条件,则构成分组条件的集合数为2。因此带有GROUP
BY的查询一般成为分组查询,也被成查询。组合记录之有点取决于组合集合(不分包重复元素)中元素的个数。例如,组合条件吧同排列,则查询结果集记录条数应该等于是列所有字段所做(数学意义上之)集合的素个数(NULL字段也好不容易一个)。如果发生少只组合列,则记录数等于实际被存在的点滴独列所做的数码。

 

SELECT XZ,DFMC
FROM ODS.DM_RPT_QYHF
WHERE XZ=’02’

GROUP BY XZ,DFMC;

02 ‘中外合作’
02 ‘中外合资’
02 ‘中外股份’
02 ‘外商投资’
02 ‘外国与港澳台地区在境经营’
02 ‘外国与港澳台地区常驻代表机关’

SELECT XZ,DFMC
FROM ODS.DM_RPT_QYHF

GROUP BY XZ,DFMC;

01 ‘其他’
01 ‘内资企业’
01 ‘国有’
01 ‘股份合作’
01 ‘集体’
02 ‘中外合作’
02 ‘中外合资’
02 ‘中外股份’
02 ‘外商投资’
02 ‘外国与港澳台地区在境经营’
02 ‘外国与港澳台地区常驻代表机构’
03 ‘个人独资’
03 ‘合伙企业’
03 ‘私营有限’
03 ‘私营股份’
‘[Null]’ ‘[Null]’

 

SELECT XZ,DFDM,DFMC,COUNT(*)
FROM ODS.DM_RPT_QYHF
GROUP BY XZ,DFDM,DFMC;


01      01      ‘国有’  4
01      02      ‘集体’  4
01      03      ‘股份合作’      3
01      04      ‘内资企业’      26
01      05      ‘其他’  2
02      01      ‘中外合资’      2
02      02      ‘中外合作’      4
02      03      ‘外商投资’      28
02      04      ‘中外股份’      4
02      06      ‘外国与港澳台地区在境经营’      1
02      07      ‘外国与港澳台地区常驻代表机关’  1
03      01      ‘私营有限’      6
03      02      ‘私营股份’      4
03      03      ‘个人独资’      1
03      04      ‘合伙企业’      1
‘[Null]’        ‘[Null]’        ‘[Null]’        43

 

GROUP
BY组合列必须出现查询的SELECT关键字背后,相同组合条件的事态下仅仅保留一个。因此,通过SELECT…GROUP
BY查询有的逐一列都应该是数量相同,要达成同等之目的,有少数栽途径:一栽是即将查询的许段方及组合条件被,一种是于未做条件的字段上运聚合函数,当然为可以当结合列上聚合函数。处子之外,别无它法!如果查询的逐一列结果数目不抵,则结果集会产出“不克针对旅”的不当。
因此,将未做条件的排列在非使聚合函数准下放至要是询问的排着,这种做法是截然错误的。

 

SELECT XZ

FROM ODS.DM_RPT_QYHF

GROUP BY XZ;


01

02

03

‘[Null]’

 

GROUP
BY于举行结合查询的时节,会指向NULL的分组单独形成一行,进行统计。参看上面的SQL。

 

GROUP
BY对做条件列来说,本身就会见自行分组(剔除重复的排),因此在成条件的列上应用DISTINCT关键字是多于的。但是就此在不做条件(都来聚合函数)的列上使用DISTINCT却非是剩下的。

 

SELECT COUNT(DISTINCT(A.QYLX_ZL))

FROM DJ_ZT A

GROUP BY SUBSTR(A.QYLX_ZL,1,1);


1       3       11

2       2       9

3       6       2

4       5       10

5       3       9

6       1       6

7       2       2

8       2       1

9       3       2

 

GROUP BY不但可以对列组合,还好针对列的表达式进行整合。

 

例如:

SELECT

COUNT(A.BS) AS HS,

B.HYML_DM AS HYML_DM,

(SELECT HYML_MC FROM DM_HYML WHERE HYML_DM=B.HYML_DM) AS HYML_MC

FROM DJ_ZT A RIGHT OUTER JOIN DM_HYML B

ON SUBSTR(A.HYLB_DM,1,2)=B.HYML_DM

GROUP BY B.HYML_DM;


2071    01      ‘农、林、牧、渔业’

17952   03      ‘制造业’

679     02      ‘采矿业’

150     04      ‘电力、燃气和和之生及供应业’

5921    05      ‘建筑业’

11406   06      ‘交通运输、仓储和邮政业’

3030    07      ‘信息传输、计算机服务及软件业’

51308   08      ‘批发与零售业’

3940    09      ‘住宿和餐饮业’

1229    10      ‘金融业’

3548    11      ‘房地产业’

6916    12      ‘租赁和商务服务业’

1003    13      ‘科学研究、技术劳务同地质勘查业’

537     14      ‘水利、环境及公共设施管理业’

11471   15      ‘居民服务和另外服务业’

44      16      ‘教育’

135     17      ‘卫生、社会保障和社会福利业’

676     18      ‘文化、体育与娱乐业’

5747    19      ‘公共管理与社会团队’

0       20      ‘国际集团’

 

得当SELECT … GROUP BY
分组后筛选数据。筛选的重要字是HAVING。HAVING的意与WHERE类似。都是故来过滤查询的中记录。但是,HAVING从句指定的每个列规范必须出现在一个聚合函数内,或者出现于GROUP
BY从句命名的列中。与WHERE不同的凡:WHERE是当分组前(查询后)筛选数据;HAVING是在分组后筛选数据。

例如:

 

SELECT

SUBSTR(A.HYLB_DM,1,2),

COUNT(*),

SUM(A.ZCZB)

FROM DJ_ZT A

GROUP BY SUBSTR(A.HYLB_DM,1,2)

HAVING MAX(YEAR(A.CJRQ))<>2007;


08      51308   2988475.0376

 

SELECT

SUBSTR(A.HYLB_DM,1,2),

COUNT(*),

SUM(A.ZCZB)

FROM DJ_ZT A

GROUP BY SUBSTR(A.HYLB_DM,1,2)

HAVING MAX(YEAR(A.CJRQ))<>2007 AND COUNT(*)>2;


08      51308   2988475.0376

 

 

又要一个较奇特的例证,对比一下探望:

SELECT SUBSTR(HY_DM,1,2), COUNT(HY_DM)

FROM DM_HY 

GROUP BY SUBSTR(HY_DM,1,2)

ORDER BY SUBSTR(HY_DM,1,2);


01      53

02      44

03      620

04      14

05      15

06      58

07      21

08      117

09      10

10      21

11      6

12      37

13      30

14      26

15      21

16      18

17      22

18      38

19      34

20      2

 

SELECT SUBSTR(HY_DM,1,2), COUNT(HY_DM)

FROM DM_HY 

GROUP BY SUBSTR(HY_DM,1,2)

HAVING COUNT(*)>100

ORDER BY SUBSTR(HY_DM,1,2);


03      620

08      117

 

自此可以看COUNT(*)是针对性各一个分组的。

 

此外,有时候可以以分组之前开展多少筛选并排序,比如:

SELECT SUBSTR(A.HY_DM,1,2)

FROM DM_HY A

WHERE SUBSTR(A.HY_DM,1,2) NOT LIKE ’01’

GROUP BY SUBSTR(A.HY_DM,1,2)

ORDER BY SUBSTR(A.HY_DM,1,2) ASC;


02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

19

20

 

 

亚、GROUP
BY的高级用法

** 

1、GROUP BY … WITH ROLLUP 上滚统计

 

SELECT SUBSTR(A.HYLB_DM,1,2),COUNT(*)

FROM DJ_ZT A

GROUP BY SUBSTR(A.HYLB_DM,1,2) WITH ROLLUP;


‘[Null]’        127847

01      2071

02      679

03      17952

04      150

05      5921

06      11406

07      3030

08      51308

09      3940

10      1229

11      3548

12      6916

13      1003

14      537

15      11471

16      44

17      135

18      676

19      5747

‘[Null]’        84

 

2、GROUP BY … WITH CUBE

 

这查询对于一个结合条件下与上滚查询的结果同样,但来多单组成条件上,此报句会发生用NULL和顺序组合字段进行匹配,形成新的记录行,并拓展统计。这个函数平时充分不常用。

 

一个结缘条件的情况:

SELECT SUBSTR(A.HYLB_DM,1,2),COUNT(*)

FROM DJ_ZT A

WHERE A.ZCZB>100

GROUP BY SUBSTR(A.HYLB_DM,1,2) WITH CUBE;


‘[Null]’        11026

01      350

02      18

03      2721

04      47

05      1228

06      235

07      292

08      2477

09      212

10      135

11      1430

12      420

13      116

14      86

15      988

16      8

17      9

18      68

19      182

‘[Null]’        4

 

 

区区独组成条件,对比一下,一目了然:

 

第一种:不加WITH CUBE条件:

SELECT SUBSTR(A.HYLB_DM,1,2),SUBSTR(A.QYLX_ZL,1,1),COUNT(*)

FROM DJ_ZT A

WHERE A.ZCZB>100

AND A.HYLB_DM IS NOT NULL

AND A.QYLX_ZL IS NOT NULL

AND SUBSTR(A.HYLB_DM,1,2) NOT
IN(’03’,’04’,’05’,’06’,’07’,’08’,’09’,’10’,’11’,’12’,’13’,’14’,’15’,’16’,’17’,’18’,’19’,’20’)

AND A.QYLX_DM=’01’

GROUP BY SUBSTR(A.HYLB_DM,1,2),SUBSTR(A.QYLX_ZL,1,1);


01      1       41

01      3       18

02      1       4

 

第二种:加上WITH CUBE条件:

SELECT SUBSTR(A.HYLB_DM,1,2),SUBSTR(A.QYLX_ZL,1,1),COUNT(*)

FROM DJ_ZT A

WHERE A.ZCZB>100

AND A.HYLB_DM IS NOT NULL

AND A.QYLX_ZL IS NOT NULL

AND SUBSTR(A.HYLB_DM,1,2) NOT
IN(’03’,’04’,’05’,’06’,’07’,’08’,’09’,’10’,’11’,’12’,’13’,’14’,’15’,’16’,’17’,’18’,’19’,’20’)

AND A.QYLX_DM=’01’

GROUP BY SUBSTR(A.HYLB_DM,1,2),SUBSTR(A.QYLX_ZL,1,1) WITH CUBE;


‘[Null]’        1       45

‘[Null]’        3       18

‘[Null]’        ‘[Null]’        63

01      ‘[Null]’        59

02      ‘[Null]’        4

01      1       41

01      3       18

02      1       4

 

其三、核心原理

 

不过生深入明这些讲话执行的经过才会就心中有数,明明白白写SQL。下面是富含WHERE和HAVING的SELECT语句执行进程:

 

1、执行WHERE筛选数据

2、执行GROUP
BY分组形成中间分组表

3、执行WITH
ROLLUP/CUBE生成统计分析数据记录并在中间分组表

4、执行HAVING筛选中分组表

5、执行ORDER BY排序

发表评论

电子邮件地址不会被公开。 必填项已用*标注