SQL中order by group by having的用法区别.docx
- 文档编号:18619110
- 上传时间:2023-08-20
- 格式:DOCX
- 页数:11
- 大小:20.34KB
SQL中order by group by having的用法区别.docx
《SQL中order by group by having的用法区别.docx》由会员分享,可在线阅读,更多相关《SQL中order by group by having的用法区别.docx(11页珍藏版)》请在冰点文库上搜索。
SQL中orderbygroupbyhaving的用法区别
orderby、groupby、having的用法区别
orderby从英文里理解就是行的排序方式,默认的为升序。
orderby后面必须列出排序的字段名,可以是多个字段名。
groupby从英文里理解就是分组。
必须有“聚合函数”来配合才能使用,使用时至少需要一个分组标志字段。
什么是“聚合函数”?
像sum()、count()、avg()等都是“聚合函数”
使用groupby的目的就是要将数据分类汇总。
一般如:
select单位名称,count(职工id),sum(职工工资)form[某表]
groupby单位名称
这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。
在sql命令格式使用的先后顺序上,groupby先于orderby。
select命令的标准格式如下:
SELECTselect_list
[INTOnew_table]
FROMtable_source
[WHEREsearch_condition]
[GROUPBYgroup_by_expression]
[HAVINGsearch_condition]
1.GROUPBY是分组查询,一般GROUPBY是和聚合函数配合使用
groupby有一个原则,就是select后面的所有列中,没有使用聚合函数的列,必须出现在groupby后面(重要)
例如,有如下数据库表:
A B
1 abc
1 bcd
1 asdfg
如果有如下查询语句(该语句是错误的,原因见前面的原则)
selectA,BfromtablegroupbyA
该查询语句的意图是想得到如下结果(当然只是一相情愿)
A B
abc
1 bcd
asdfg
右边3条如何变成一条,所以需要用到聚合函数,如下(下面是正确的写法):
selectA,count(B)as数量fromtablegroupbyA
这样的结果就是
A数量
1 3
2.Having
where子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
having子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having条件显示特定的组,也可以使用多个分组标准进行分组。
having子句被限制子已经在SELECT语句中定义的列和聚合表达式上。
通常,你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值,就如你在SELECT语句中做的那样。
例如:
SELECTACOUNT(B)FROMTABLEGROUPBYAHAVINGCOUNT(B)>2
Grouping的用法:
指示是否聚合groupby列表中的指定表达式。
在结果集中,如果Grouping返回1,表示聚合;如果Grouping返回0,表示非聚合。
如果指定了Groupby,那么只能用在Select,Having,Orderby中。
注释:
GROUPING用于区分标准空值和由ROLLUP、CUBE或GROUPINGSETS返回的空值。
作为ROLLUP、CUBE或GROUPINGSETS操作结果返回的NULL是NULL的特殊应用。
它在结果集内作为列的占位符,表示全体。
举例:
CREATETABLEtt(产地CHAR(8),水果CHAR(8),重量INT)
INSERTttVALUES('北方','香蕉',3)
INSERTttVALUES('北方','水蜜桃',2)
INSERTttVALUES('南方','桔子',3)
INSERTttVALUES('北方','水蜜桃',5)
INSERTttVALUES('南方','香蕉',3)
INSERTttVALUES('南方','水蜜桃',6)
INSERTttVALUES('北方','桔子',8)
select
CASEWHEN(GROUPING(产地)=1)THEN'总计'
ELSEISNULL(产地,'UNKNOWN')
ENDAS产地,
CASEWHEN(GROUPING(水果)=1)THEN'小计'
ELSEISNULL(水果,'UNKNOWN')
ENDAS产地,
SUM(重量)总重量
FROMTT
GROUPBY产地,水果
WITHROLLUP
结果:
/************************
北方桔子8
北方水蜜桃7
北方香蕉3
北方小计18
南方桔子3
南方水蜜桃6
南方香蕉3
南方小计12
总计小计30
*************************/
GROUPING(字段)=1的是对应字段汇总的
GROUPING(字段)=0的是对应字段原来的明细的信息
oracleRollup和Cube用法
Oracle的GROUPBY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。
如果是ROLLUP(A,B,C)的话,首先会对(A、B、C)进行GROUPBY,然后对(A、B)进行GROUPBY,然后是(A)进行GROUPBY,最后对全表进行GROUPBY操作。
如果是GROUPBYCUBE(A,B,C),则首先会对(A、B、C)进行GROUPBY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUPBY操作。
grouping_id()可以美化效果:
Oracle的GROUPBY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。
除本文内容外,你还可参考:
分析函数参考手册:
分析函数使用例子介绍:
SQL>createtabletasselect*fromdba_indexes;表已创建。
SQL>selectindex_type,status,count(*)fromtgroupbyindex_type,status;
INDEX_TYPESTATUSCOUNT(*)
---------------------------------------------
LOBVALID51
NORMALN/A25
NORMALVALID479
CLUSTERVALID11
下面来看看ROLLUP和CUBE语句的执行结果。
SQL>selectindex_type,status,count(*)fromtgroupbyrollup(index_type,status);
INDEX_TYPESTATUSCOUNT(*)
---------------------------------------------
LOBVALID51
LOB51
NORMALN/A25
NORMALVALID479
NORMAL504
CLUSTERVALID11
CLUSTER11
566
已选择8行。
SQL>selectindex_type,status,count(*)fromtgroupbycube(index_type,status);
INDEX_TYPESTATUSCOUNT(*)
---------------------------------------------
566
N/A25
VALID541
LOB51
LOBVALID51
NORMAL504
NORMALN/A25
NORMALVALID479
CLUSTER11
CLUSTERVALID11
已选择10行。
查询结果不是很一目了然,下面通过Oracle提供的函数GROUPING来整理一下查询结果。
SQL>selectgrouping(index_type)g_ind,grouping(status)g_st,index_type,status,count(*)
2fromtgroupbyrollup(index_type,status)orderby1,2;
G_INDG_STINDEX_TYPESTATUSCOUNT(*)
-----------------------------------------------------------------
00LOBVALID51
00NORMALN/A25
00NORMALVALID479
00CLUSTERVALID11
01LOB51
01NORMAL504
01CLUSTER11
11566
已选择8行。
这个查询结果就直观多了,和不带ROLLUP语句的GROUPBY相比,ROLLUP增加了对INDEX_TYPE的GROUPBY统计和对所有记录的GROUPBY统计。
就是说,如果是ROLLUP(A,B,C)的话,首先会对(A、B、C)进行GROUPBY,然后对(A、B)进行GROUPBY,然后是(A)进行GROUPBY,最后对全表进行GROUPBY操作。
下面看看CUBE语句。
SQL>selectgrouping(index_type)g_ind,grouping(status)g_st,index_type,status,count(*)
2fromtgroupbycube(index_type,status)orderby1,2;
G_INDG_STINDEX_TYPESTATUSCOUNT(*)
-----------------------------------------------------------------
00LOBVALID51
00NORMALN/A25
00NORMALVALID479
00CLUSTERVALID11
01LOB51
01NORMAL504
01CLUSTER11
10N/A25
10VALID541
11566
已选择10行。
和ROLLUP相比,CUBE又增加了对STATUS列的GROUPBY统计。
如果是GROUPBYCUBE(A,B,C),则首先会对(A、B、C)进行GROUPBY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUPBY操作。
除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUPBY结果。
SQL>selectgrouping_id(index_type,status)g_ind,index_type,status,count(*)
2fromtgroupbyrollup(index_type,status)orderby1;
G_INDINDEX_TYPESTATUSCOUNT(*)
-------------------------------------------------------
0LOBVALID51
0NORMALN/A25
0NORMALVALID479
0CLUSTERVALID11
1LOB51
1NORMAL504
1CLUSTER11
3566
已选择8行。
SQL>selectgrouping_id(index_type,status)g_ind,index_type,status,count(*)
2fromtgroupbycube(index_type,status)orderby1;
G_INDINDEX_TYPESTATUSCOUNT(*)
-------------------------------------------------------
0LOBVALID51
0NORMALN/A25
0NORMALVALID479
0CLUSTERVALID11
1LOB51
1NORMAL504
1CLUSTER11
2N/A25
2VALID541
3566
已选择10行。
grouping_id()可以美化效果:
selectDECODE(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)
fromT2
groupbyrollup(C1,C2,C1+C2,C1+1,C2+1);
===========================================================
1.报表合计专用的Rollup函数
销售报表
广州1月2000元
广州2月2500元
广州4500元
深圳1月1000元
深圳2月2000元
深圳3000元
所有地区7500元
以往的查询SQL:
Selectarea,month,sum(money)fromSaleOrdergroupbyarea,month
然后广州,深圳的合计和所有地区合计都需要在程序里自行累计
1.其实可以使用如下SQL:
Selectarea,month,sum(total_sale)fromSaleOrdergroupbyrollup(area,month)
就能产生和报表一模一样的纪录
2.如果year不想累加,可以写成
Selectyear,month,area,sum(total_sale)fromSaleOrdergroupbyyear,rollup(month,area)
另外Oracle9i还支持如下语法:
Selectyear,month,area,sum(total_sale)fromSaleOrdergroupbyrollup((year,month),area)
3.如果使用Cube(area,month)而不是RollUp(area,month),除了获得每个地区的合计之外,还将获得每个月份的合计,在报表最后显示。
4.Grouping让合计列更好读
RollUp在显示广州合计时,月份列为NULL,但更好的做法应该是显示为"所有月份"Grouping就是用来判断当前Column是否是一个合计列,1为yes,然后用Decode把它转为"所有月份"
SelectDecode(Grouping(area),1,'所有地区',area)area,Decode(Grouping(month),1,'所有月份',month),sum(money)FromSaleOrderGroupbyRollUp(area,month);
2.对多级层次查询的startwith.....connectby
比如人员组织,产品类别,Oracle提供了很经典的方法
SELECTLEVEL,name,emp_id,manager_emp_idFROMemployeeSTARTWITHmanager_emp_idisnullCONNECTBYPRIORemp_id=manager_emp_id;
上面的语句demo了全部的应用,startwith指明从哪里开始遍历树,如果从根开始,那么它的manager应该是Null,如果从某个职员开始,可以写成emp_id='11'
CONNECTBY就是指明父子关系,注意PRIOR位置
另外还有一个LEVEL列,显示节点的层次
3.更多报表/分析决策功能
3.1分析功能的基本结构
分析功能()over(partion子句,orderby子句,窗口子句)
概念上很难讲清楚,还是用例子说话比较好.
3.2Row_Number和Rank,DENSE_Rank
用于选出Top3sales这样的报表
当两个业务员可能有相同业绩时,就要使用Rank和Dense_Rank
比如
金额RowNumRankDense_Rank
张三4000元111
李四3000元222
钱五2000元333
孙六2000元433
丁七1000元554
这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。
SELECTsalesperson_id,SUM(tot_sales)sp_sales,RANK()OVER(ORDERBYSUM(tot_sales)DESC)sales_rankFROMordersGROUPBYsalesperson_id
3.3NTILE把纪录平分成甲乙丙丁四等
比如我想取得前25%的纪录,或者把25%的纪录当作同一个level平等对待,把另25%当作另一个Level平等对待
SELECTcust_nbr,SUM(tot_sales)cust_sales,NTILE(4)OVER(ORDERBYSUM(tot_sales)DESC)sales_quartileFROMordersGROUPBYcust_nbrORDERBY3,2DESC;
NTITLE(4)把纪录以SUM(tot_sales)排序分成4份.
3.4辅助分析列和WindowsFunction
报表除了基本事实数据外,总希望旁边多些全年总销量,到目前为止的累计销量,前后三个月的平均销量这样的列来参考.
这种前后三个月的平均和到目前为止的累计销量就叫windowsfunction,见下例
SELECTmonth,SUM(tot_sales)monthly_sales,SUM(SUM(tot_sales))OVER(ORDERBYmonthROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)max_preceedingFROMordersGROUPBYmonthORDERBYmonth;
SELECTmonth,SUM(tot_sales)monthly_sales,AVG(SUM(tot_sales))OVER(ORDERBYmonthROWSBETWEEN1PRECEDINGAND1FOLLOWING)rolling_avgFROMordersGROUPBYmonthORDERBYmonth;
WindowsFunction的关键就是Windows子句的几个取值
1PRECEDING之前的一条记录
1FOLLOWING之后的一条记录
UNBOUNDEDPRECEDING之前的所有记录
CURRENTROW当前纪录
4.SubQuery总结
SubQuery天天用了,理论上总结一下.SubQuery分三种
1.Noncorrelated子查询最普通的样式.
2.CorrelatedSubqueries把父查询的列拉到子查询里面去,头一回cyt教我的时候理解了半天.
3.InlineView也被当成最普通的样式用了.
然后Noncorrelated子查询又有三种情况
1.返回一行一列whereprice<(selectmax(price)fromgoods)
2.返回多行一列whereprice>=ALL(selectpricefromgoodswheretype=2)
orwhereNOTprice 最常用的IN其实就是=ANY() 3.返回多行多列一次返回多列当然就节省了查询时间 UPDATEmonthly_ordersSET(tot_orders,max_order_amt)=(SELECTCOUNT(*),MAX(sale_price)FROMcust_order)DELETEFROMline_itemWHERE(order_nbr,part_nbr)IN(SELECTorder_nbr,part_nbrFROMcust_orderc) ======================================== /*--------理解groupingsets selecta,b,c,sum(d)fromt groupbygroupingsets(a,b,c) 等效于 select*from( selecta,null,null,sum(d)fromtgroupbya unionall selectnull,b,null,sum(d)fromtgroupbyb unionall selectnull,null,c,sum(d)fromtgroupbyc ) */
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL中order by group having的用法区别 SQL order having 用法 区别
![提示](https://static.bingdoc.com/images/bang_tan.gif)