oracle 增加字段顺心,Oracle Group By 用法之 —— Having_79057

Oracle Group By 用法之

—— Having

转自：http://blog.csdn.net/zhaozhongju/archive/2009/05/13/4177047.aspx

客户需求分析：

笔者最近接到一家客户的一个需求。他们安排了一个ERP系统，目前批准的即便Oracle数据库。目前由于企业普查分析的必需，要告终如下的需求。

1、按月份来普查2009年第一季度每个供给商的采购金额。也即便说，在报表中要能够揭示出2009年1月份、2月份、3月份供给商的采购金额协商，无须要明细。

2、揭示的收获按年度、月份、供给商名字举行排序。

归纳客户的要求，也即便说要告终如上这张报表。

PL/SQL语句解析：

select extract(YEAR FROM t.dateordered) AS 年度,extract(MONTH

FROM t.dateordered) as 月份,

p.name as 供给商名字,sum(t.linenetamt) 协商

from c_orderline2 t

left join c_bpartner p on p.c_bpartner_id=t.c_bpartner_id

group by extract(YEAR FROM t.dateordered),extract(MONTH FROM

t.dateordered),p.name

having extract(YEAR FROM t.dateordered)=2009 and extract(MONTH

FROM t.dateordered) in (1,2,3)

order by p.name;

穿越以上语句就能够告终企业如上的需求。在这个需求中，笔者重要用过Group

By语句与Having语句来告终。这两个是Oralce数据库中PL/SQL语言中两个很重要的分组语句。利用这个两个语句能够告终一些混杂的普查功能。对于Group

By与Having语句的一些大约用法，笔者在这里不做过多描写。笔者这里想说的是，在利用这两个语句举行数据普查时必需当心的地方。在Oracle数据库系统中，对于这两个普查子句做了比拟严厉的利用局限。数据库管教员定然对这些利用闲置铭刻在心，否则的话很轻率在普查的过程中遭到讹谬。翔实来说，有如下几个利用局限。

1、万一抉择列表中包括有列、表白式可能分组函数，那么这些列可能表白式定然展目前Group

By子句中，否则数据库会提醒相干的讹谬消息。分组函数无须展目前Group

By子句中。如上面这个例子，由于在数据库基础表中存储的是下订单的日期，如2009年4月15日。也即便说，年月日是存储在统一个字段中的。然而在普查的时候，必需普查2009年1月、2月、3月的供给商采购金额。为此此刻笔者先利用Extract函数从一个日期数据中抽取翔实的年、月消息。这个是Oracle数据库中一个很有用的日期函数。要是未曾这个函数的话，笔者还必需穿越字符串等处理函数来截取年月等消息。由于Extract是一个带函数的表白式，为此其定然展目前Group

By子句中。而且当心，笔者此刻批准的是表白式本身，而不是其别名。也即便说，笔者未曾批准group

by年度，月份等表白措施。也即便说，在Group

By子句中，定然批准表白式的全称，而不能够批准其别名。否则的话，数据库系统不会接受这个语句。数据库系统之所有要举行相仿的扼制，其暗中藏有比拟深层次的起因。不过作为等闲数据库管教员来说，不怎么用打听数据库设计暗中的内容。只必需把这个法定记在心中即可。这个法定对于大局部数据库管教员来说，可能会经常背弃他。为此笔者再次偏重一遍，抉择列表中万一包括有列、表白式时，这个列、表白式定然包括在Group

By子句中。另外，万一批准了表白式的话，则数据库管教员即便在抉择列表中批准了别名，然而在Group

By子句中依旧定然批准表白式的全面表白措施，而不能够批准别名。

万一在一个查询语句中，同时含有Group By(分组语句)、Having(分组语句下的条件函数)、Order

By(排序语句)三个共存的话，则必需当心他们有定然的书写次序。通常情形下Order

By排序语句定然放置在最后。如上面的案例中，笔者就把这个排序语句放在最末路的地方。万一不这么处理的话，系统编译器是不会接受这个PL/SQL语句的。另外必需当心的是，批准Group语句会积极对纪录举行排序。如上面的语句中，笔者并未曾对年份、月度举行排序，而只是按供给商名目来举行排序。而切实收获的话，却会积极按年度、月份的大小从小到大来举行排序。这重要因为Group

By子句在普查之前，会先对登记按照Group

By中的参数从左到右来举行排序，然后再举行普查。如此的话，最后揭示的收获即便曾经排过序来的收获。万一数据库管教员对于这个排序收获不顺心的话，就必需穿越Order

By子句再来对揭示收获举行排序。不过这里必需当心的是，本身分组等子句就必需对纪录举行排序并举行定然的业务逻辑处理，此刻会花费比拟多的数据库资源。为此从数据库的功能琢磨，在利用Order

By排序子句对普验收获举行重新排序的时候，要端庄。万一想把某个字段当作第顺次序排序的话，则只必需把这个字段放置在分组语句中的第一个参数即可。如在上面这个案例中，笔者全面能够穿越group

by p.name,extract(YEAR FROM

t.dateordered)www.the9ti.info,extract(MONTH FROM

t.dateordered)这个分组语句来告终收获的排序，而能够不再利用Order

By语句。为此只有在必需的情形下，能力够利用Order By子句。

Group By子句与Where子句是不接受的。也即便说，在等闲的Select等语句中(不含有Group

By子句)时能够利用Where子句来过滤揭示的收获。然而在上面的语句中baozhang.org，笔者却是利用Having语句来过滤揭示收获的。这重要是因为Group

By子句与Where子句是不接受的。也即便说，当要揭示分组揭示收获时，数据库管教员定然要利用Having子句，而不能够在Where自居中利用分组函数局限分组揭示收获。万一数据库管教员在这里背弃这条分寸话，那么数据库系统就会拒绝接受这条语句。数据库会提醒讹谬消息，如“www.zhongxiaoxue.org号召未曾准确告终”等等。另外Having语句重要用来局限分组普验收获，其跟Group

By语句是双胞胎。即Having子句定然跟在Group By语句后面利用。其中Group

By子句用户对查询收获举行分组普查，而Having子句则用于局限分组揭示收获，即依据用户的要求来局部揭示所必需的内容。他们是互相配合，分工配合。能够说，万一未曾Having语句的帮助，那么Group

By语句会失色许多。另外，Having语句对于长进数据库与利用服务器的功能也有很大的联系。由于批准了Having语句来过滤揭示的收获，那么其最后相称条件的收获确定比未曾设置局限条件的要少的多。这也就意味着这个揭示收获能够收缩数据在网络中的传输，良好配上Having

条件过滤语句(固然这并不属于迫使局限)。如在在ERP系统中生成这张报表的时候，能够提醒用户输入参数，如必需普查几几年几月份到几几年几月份的采购金额。万一用户未曾输入相干的数据的话，则其默感受最近一年的普查数据等等。这固然是一个小小的技巧，然而在数据量比拟多可能数据分组波及到多张表的情形下，能够极其管用的长进数据库与利用服务器的功能。这是一个迫使的条件，数据库管教员定然无条件的顺从。

必需当心多列分组的次序问题。group by p.name,extract(YEAR FROM

t.dateordered),extract(MONTH FROM t.dateordered 与group by

extract(YEAR FROM t.dateordered),extract(MONTH FROM t.dateordered

,p.name这两个分组语句有什么差异吗?万一光从收获上来说，是未曾多大差异的。然而从其内部的处理机制上来说，有很大的差异。这重要波及到多列分组的问题。多列分组时指在Group

By子句中利用两个可能两个以上的列生成分组普验收获。当举行多列分组时，聚集于多个列的不同值发生数据普验收获。如上面的例子中，数据库就会依据年份、月份、供给商来生成普验收获。万一用前者的表白措施，则其先普查每个供给商的协商金额，在分类普查年、月的普查金额。容易的说，他们普查次序不同，然而揭示收获雷同。这也是第一点所说的，要把抉择列表中的字段名、表白式等等全副纳入到Group

By子句中的起因。只有如此，Group By才会对这些字段举行分组普查。只有如此能力够保证，不会因为Group

By子句中的参数次序不同，而揭示不同的普验收获。

可见Group

By分组语句其利用规范要比等闲的Select等查询语句要求严厉的多。数据库管教员在利用Group语句举行数据分组普查时，定然要当心这些利用局限。否则的话在利用这个分组语句的时候，难免会磕磕碰碰的。我们打听，验证“任何一个>=6之偶数，都能够表示成两个奇质数之和

”是个难题，