Group by

用途：

对结果集进行分组，常与汇总函数一起使用。

语法：

SELECT column,SUM(column) FROM table GROUP BY column 例：

“Sales” 表中的原始数据 :

Company Amount

W3Schools 5500

IBM 4500

W3Schools 7100

按照 Company 字段进行分组，求出每个 Company 的 Amout 的合计： SELECT Company,SUM(Amount) FROM Sales GROUP BY Company 返回结果 ：

Company SUM(Amount)

W3Schools 12600

IBM 4500

Having

用途：

指定群组或汇总的搜寻条件。

语法：

SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

解释：

HAVING 通常与 GROUP BY 子句同时使用。不使用 GROUP BY 时， HAVING 则与 WHERE 子句功能相似。

例：

“Sales” 表中的原始数据 :

Company Amount

W3Schools 5500

IBM 4500

W3Schools 7100

按照 Company 字段进行分组，求出每个 Company 的 Amout 的合计在 10000 以上的数据：

SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000

返回结果 ：

Company SUM(Amount)

W3Schools 12600

 

第二篇：order by 、group by 、having的用法区别

Having

这个是用在聚合函数的用法。当我们在用聚合函数的时候，一般都要用到GROUP BY 先进行分组，然后再进行聚合函数的运算。运算完后就要用到HAVING 的用法了，就是进行判断了，例如说判断聚合函数的值是否大于某一个值等等。

select customer_name,sum(balance)

from balance

group by customer_name

having balance>200; yc_rpt_getnew

order by 、group by 、having的用法区别

order by 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。

group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。

什么是“聚合函数”？

像sum()、count()、avg()等都是“聚合函数”

使用group by 的目的就是要将数据分类汇总。

一般如：

select 单位名称,count(职工id),sum(职工工资) form [某表]

group by 单位名称

1

这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。

在sql命令格式使用的先后顺序上，group by 先于 order by。 select 命令的标准格式如下：

SELECT select_list

[ INTO new_table ]

FROM table_source

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

1. GROUP BY 是分组查询, 一般 GROUP BY 是和聚合函数配合使用

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面（重要）

例如,有如下数据库表：

A B

1 abc

1 bcd

1 asdfg

2

如果有如下查询语句（该语句是错误的，原因见前面的原则） select A,B from table group by A

该查询语句的意图是想得到如下结果(当然只是一相情愿)

A B

abc

1 bcd

asdfg

右边3条如何变成一条,所以需要用到聚合函数，如下(下面是正确的写法):

select A,count(B) as 数量 from table group by A

这样的结果就是

A 数量

1 3

2. Having

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

3

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常，你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样。例如：

SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2

Grouping 的用法：

指示是否聚合 group by 列表中的指定表达式。在结果集中，如果 Grouping 返回 1 ，表示聚合；如果 Grouping 返回 0 ，表示非聚合。如果指定了 Group by ，那么只能用在 Select ， Having ， Order by 中。

注释：

GROUPING 用于区分标准空值和由 ROLLUP 、 CUBE 或 GROUPING SETS 返回的空值。作为 ROLLUP 、 CUBE 或 GROUPING SETS 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，表示全体。

举例：

CREATE TABLE tt ( 产地 CHAR ( 8), 水果 CHAR ( 8), 重量 4

INT )

INSERT tt VALUES ( ' 北方 ' , ' 香蕉 ' , 3)

INSERT tt VALUES ( ' 北方 ' , ' 水蜜桃 ' , 2)

INSERT tt VALUES ( ' 南方 ' , ' 桔子 ' , 3)

INSERT tt VALUES ( ' 北方 ' , ' 水蜜桃 ' , 5)

INSERT tt VALUES ( ' 南方 ' , ' 香蕉 ' , 3)

INSERT tt VALUES ( ' 南方 ' , ' 水蜜桃 ' , 6)

INSERT tt VALUES ( ' 北方 ' , ' 桔子 ' , 8)

select

CASE WHEN ( GROUPING ( 产地 ) = 1) THEN ' 总计 ' ELSE ISNULL ( 产地 , 'UNKNOWN' )

END AS 产地 ,

CASE WHEN ( GROUPING ( 水果 ) = 1) THEN ' 小计 ' ELSE ISNULL ( 水果 , 'UNKNOWN' )

END AS 产地 ,

SUM ( 重量 ) 总重量

FROM TT

GROUP BY 产地 , 水果

WITH ROLLUP

结果：

/************************

北方 桔子 8

5

北方 水蜜桃 7

北方 香蕉 3

北方 小计 18

南方 桔子 3

南方 水蜜桃 6

南方 香蕉 3

南方 小计 12

总计 小计 30

*************************/

GROUPING(字段)=1的是对应字段汇总的

GROUPING(字段)=0的是对应字段原来的明细的信息

oracle Rollup 和 Cube用法

6

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP

和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，

(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果：

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP

和CUBE语句。

除本文内容外，你还可参考：

分析函数参考手册： /post/419/33028 分析函数使用例子介绍：

/post/419/44634

SQL> create table t as select * from dba_indexes; 表已创建。 SQL> select index_type, status, count(*) from t group by index_type, status;

INDEX_TYPE STATUS COUNT(*)

--------------------------- -------- ----------

LOB VALID 51

NORMAL N/A 25

NORMAL VALID 479

7

CLUSTER VALID 11

下面来看看ROLLUP和CUBE语句的执行结果。

SQL> select index_type, status, count(*) from t group by rollup(index_type, status);

INDEX_TYPE STATUS COUNT(*)

--------------------------- -------- ----------

LOB VALID 51

LOB 51

NORMAL N/A 25

NORMAL VALID 479

NORMAL 504

CLUSTER VALID 11

CLUSTER 11

566

已选择8行。

SQL> select index_type, status, count(*) from t group by cube(index_type, status);

INDEX_TYPE STATUS COUNT(*)

--------------------------- -------- ----------

566

8

N/A 25

VALID 541

LOB 51

LOB VALID 51

NORMAL 504

NORMAL N/A 25

NORMAL VALID 479

CLUSTER 11

CLUSTER VALID 11

已选择10行。

查询结果不是很一目了然，下面通过Oracle提供的函数GROUPING来整理一下查询结果。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)

2 from t group by rollup(index_type, status) order by 1, 2; G_IND G_ST INDEX_TYPE STATUS COUNT(*)

---------- ---------- --------------------------- -------- ----------

0 0 LOB VALID 51

0 0 NORMAL N/A 25

0 0 NORMAL VALID 479

9

0 0 CLUSTER VALID 11

0 1 LOB 51

0 1 NORMAL 504

0 1 CLUSTER 11

1 1 566

已选择8行。

这个查询结果就直观多了，和不带ROLLUP语句的GROUP BY相比，ROLLUP增加了对INDEX_TYPE的GROUP BY统计和对所有记录的GROUP BY统计。

就是说，如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

下面看看CUBE语句。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)

2 from t group by cube(index_type, status) order by 1, 2;

G_IND G_ST INDEX_TYPE STATUS COUNT(*)

---------- ---------- --------------------------- -------- 10

----------

0 0 LOB VALID 51

0 0 NORMAL N/A 25

0 0 NORMAL VALID 479

0 0 CLUSTER VALID 11

0 1 LOB 51

0 1 NORMAL 504

0 1 CLUSTER 11

1 0 N/A 25

1 0 VALID 541

1 1 566

已选择10行。

和ROLLUP相比，CUBE又增加了对STATUS列的GROUP BY统计。

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY结果。

SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)

2 from t group by rollup(index_type, status) order by 1; 11

G_IND INDEX_TYPE STATUS COUNT(*)

---------- --------------------------- -------- ---------- 0 LOB VALID 51

0 NORMAL N/A 25

0 NORMAL VALID 479

0 CLUSTER VALID 11

1 LOB 51

1 NORMAL 504

1 CLUSTER 11

3 566

已选择8行。

SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)

2 from t group by cube(index_type, status) order by 1;

G_IND INDEX_TYPE STATUS COUNT(*)

---------- --------------------------- -------- ---------- 0 LOB VALID 51

0 NORMAL N/A 25

12

0 NORMAL VALID 479

0 CLUSTER VALID 11

1 LOB 51

1 NORMAL 504

1 CLUSTER 11

2 N/A 25

2 VALID 541

3 566

已选择10行。

grouping_id()可以美化效果：

select DECODE(GROUPING_ID(C1), 1, '合计', C1) D1,

DECODE(GROUPING_ID(C1, C2), 1, '小计', C2) D2,

DECODE(GROUPING_ID(C1, C2, C1 + C2), 1, '小计', C1 + C2) D3, count(*),

GROUPING_ID(C1, C2, C1 + C2, C1 + 1, C2 + 1),

GROUPING_ID(C1)

from T2

group by rollup(C1, C2, C1 + C2, C1 + 1, C2 + 1);

===========================================================

1.报表合计专用的Rollup函数

销售报表

广州 1月 2000元

13

广州 2月 2500元

广州 4500元

深圳 1月 1000元

深圳 2月 2000元

深圳 3000元

所有地区 7500元

以往的查询SQL:

Select area,month,sum(money) from SaleOrder group by

area,month

然后广州，深圳的合计和所有地区合计都需要在程序里自行累计

1.其实可以使用如下SQL:

Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)

就能产生和报表一模一样的纪录

2.如果year不想累加，可以写成

Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)

另外Oracle 9i还支持如下语法:

Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)

3.如果使用Cube(area,month)而不是RollUp(area,month)，除了获得每个地区的合计之外，还将获得每个月份的合计，在报表最后显示。 14

4.Grouping让合计列更好读

RollUp在显示广州合计时，月份列为NULL，但更好的做法应该是显示为"所有月份" Grouping就是用来判断当前Column是否是一个合计列，1为yes，然后用Decode把它转为"所有月份"

Select Decode(Grouping(area),1,'所有地区',area) area,

Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);

2.对多级层次查询的start with.....connect by

比如人员组织,产品类别,Oracle提供了很经典的方法

SELECT LEVEL, name, emp_id,manager_emp_id FROM employee START WITH manager_emp_id is null CONNECT BY PRIOR emp_id = manager_emp_id;

上面的语句demo了全部的应用,start with指明从哪里开始遍历树,如果从根开始,那么它的manager应该是Null,如果从某个职员开始,可以写成emp_id='11'

CONNECT BY 就是指明父子关系,注意PRIOR位置

另外还有一个LEVEL列,显示节点的层次

3.更多报表/分析决策功能

3.1 分析功能的基本结构

分析功能() over( partion子句,order by子句,窗口子句) 概念上很难讲清楚,还是用例子说话比较好.

3.2 Row_Number 和 Rank, DENSE_Rank

15

用于选出Top 3 sales这样的报表

当两个业务员可能有相同业绩时,就要使用Rank和Dense_Rank 比如

金额 RowNum Rank Dense_Rank

张三 4000元 1 1 1

李四 3000元 2 2 2

钱五 2000元 3 3 3

孙六 2000元 4 3 3

丁七 1000元 5 5 4

这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。 SELECT salesperson_id, SUM(tot_sales) sp_sales, RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank FROM orders GROUP BY salesperson_id

3.3 NTILE 把纪录平分成甲乙丙丁四等

比如我想取得前25%的纪录,或者把25%的纪录当作同一个level平等对待,把另25%当作另一个Level平等对待

SELECT cust_nbr, SUM(tot_sales) cust_sales, NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile FROM orders GROUP BY cust_nbr ORDER BY 3,2 DESC;

NTITLE(4)把纪录以 SUM(tot_sales)排序分成4份.

3.4 辅助分析列和Windows Function

16

报表除了基本事实数据外,总希望旁边多些全年总销量,到目前为止的累计销量,前后三个月的平均销量这样的列来参考.

这种前后三个月的平均和到目前为止的累计销量就叫windows function, 见下例

SELECT month, SUM(tot_sales) monthly_sales,

SUM(SUM(tot_sales)) OVER (ORDER BY month ROWS BETWEEN

UNBOUNDED PRECEDING AND CURRENT ROW) max_preceeding FROM orders GROUP BY month ORDER BY month;

SELECT month, SUM(tot_sales) monthly_sales,

AVG(SUM(tot_sales)) OVER (ORDER BY month ROWS BETWEEN 1

PRECEDING AND 1 FOLLOWING) rolling_avg FROM orders GROUP BY month ORDER BY month;

Windows Function的关键就是Windows子句的几个取值

1 PRECEDING 之前的一条记录

1 FOLLOWING 之后的一条记录

UNBOUNDED PRECEDING 之前的所有记录

CURRENT ROW 当前纪录

4.SubQuery总结

SubQuery天天用了,理论上总结一下.SubQuery 分三种

1.Noncorrelated 子查询 最普通的样式.

2.Correlated Subqueries 把父查询的列拉到子查询里面去,头一回cyt教我的时候理解了半天.

17

3.Inline View 也被当成最普通的样式用了.

然后Noncorrelated 子查询又有三种情况

1.返回一行一列 where price < (select max(price) from goods )

2.返回多行一列 where price>= ALL (select price from goods where type=2)

or where NOT price< ANY(select price from goods where type=2) 最常用的IN其实就是=ANY()

3.返回多行多列 一次返回多列当然就节省了查询时间

UPDATE monthly_orders SET (tot_orders, max_order_amt) =

(SELECT COUNT(*), MAX(sale_price) FROM cust_order) DELETE FROM line_item WHERE (order_nbr, part_nbr) IN (SELECT order_nbr, part_nbr FROM cust_order c)

========================================

/*--------理解grouping sets

select a, b, c, sum( d ) from t

group by grouping sets ( a, b, c )

等效于

select * from (

select a, null, null, sum( d ) from t group by a

union all

select null, b, null, sum( d ) from t group by b

union all

18

select null, null, c, sum( d ) from t group by c )

*/

19