现在的位置: 首页 > 综合 > 正文

查询之order by,group by和having的使用

2013年10月13日 ⁄ 综合 ⁄ 共 4306字 ⁄ 字号 评论关闭

转自 http://blog.sina.com.cn/s/blog_446674910100eamj.html

1.  order by
ORDER BY子句的语法为:

SELECT column1, SUM(column2) FROM "list-of-tables"
ORDER BY "column-list" [ASC | DESC];

[ ] = optional

ORDER BY是一个可选的子句,它允许你根据指定要order by的列来以上升或者下降的顺序来显示查询的结果。例如:
ASC = Ascending Order –这个是缺省的
DESC = Descending Order

下面举个例子:

SELECT employee_id, dept, name, age, salary
FROM employee_info
WHERE dept = 'Sales'
ORDER BY salary;

这条SQL语句将从employee_info表中列dept等于'Sales'选择employee_id,、dept、name、age和salary,并且根据他们的salary按升序的顺序来列出检索结果。

如果你想对多列排序的话,那么在列与列之间要加上逗号,比如:

SELECT employee_id, dept, name, age, salary
FROM employee_info
WHERE dept = 'Sales'
ORDER BY salary, age DESC;

2.  group by
1) 、首先讲讲GROUP BY子句语法:

SELECT column1, SUM(column2) FROM "list-of-tables"
GROUP BY "column-list";

这个GROUP BY子句将集中所有的行在一起,它包含了指定列的数据以及允许合计函数来计算一个或者多个列。当然最好解释的方法是给出一个例子啦:
假设我们将从employee表中搜索工资最高的列,可以使用以下的SQL语句:

SELECT max(salary), dept
FROM employee
GROUP BY dept;

这条语句将在每一个单独的部门中选择工资最高的工资。结果他们的salary和dept将被返回。

2)、group by从英文里理解就是分组必须有“聚合函数”来配合才能使用,使用时至少需要一个分组标志字段。
 
什么是“聚合函数”?
 像sum()、count()、avg()等都是“聚合函数”
 使用group by的目的就是要将数据分类汇总。

 一般如:
    select单位名称,count(职工id),sum(职工工资) form [某表]
    group by单位名称
   这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。

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

   SELECT select_list
   [ INTO new_table ]
   FROM table_source
   [ WHERE search_condition ]
   [ GROUP BY group_by_expression_r ]
   [ HAVING search_condition ]
   [ ORDER BY order_expression_r [ ASC | DESC ] ]

3) 理解group by:
    表1:

 type

udate

 mp3

2006-3-2

flash

 2006-1-2

mp3

2006-2-2

 

 


在表1中,假设要取出最后更新的某一类型(type)产品的日期
那么就要使用group by type的方式

select type from 表1 group by type

但这样就无法读到udate
 
再来

select type,udate from表1 group by type

这种写法是错误的。
原因是type和udate不是一对一的关系。就是说一种type有几个udate. 所以
selct ** group by **之后没办法组织形成新的表,不知道要取哪个udate
 
再来

select type,max(udate) from 表1 group by type

这样就正确了。 取的是最大的udate,一对一成立。

4) GROUP BY...被附加于SQL是因为聚会函数(如SUM)每次都返回所有列值的合计,没有GROUP BY函数是不可能找到列值的单个分组的合计数。

语法

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

-----------------------------------------------
GROUP BY示例
这个"Sales"表:

 Company

Amount

 W3Schools

5500

IBM

4500

W3Schools

7100

 

 

和这个SQL语句:

SELECT Company, SUM(Amount) FROM Sales

返回这个结果:

 Company

SUM(Amount)

 W3Schools

5500

IBM

4500

W3Schools

7100

 

 

上面的代码是不正确的,因为被返回的列不是部分合计。GROUP BY子句将解决这个问题。

SELECT Company,SUM(Amount) FROM Sales GROUP BY Company

返回结果:

 Company

SUM(Amount)

 W3Schools

12600

IBM

4500

5) SQL Group by学习及相关应注意的地方
   在select语句中可以使用group by子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息
,另外,可以使用having子句限制返回的结果集。group by子句可以将查询结果分组,并返回行的汇总信息按照group by子句中指定的表达式的值分组查询结果。
         在带有group by子句的查询语句中,在select列表中指定的列要么是group by子句中指定的列,要么包含聚组函数

     select max(sal),job emp group by job;

          (注意max(sal),job的job并非一定要出现,但有意义)
          查询语句的select和group by ,having子句是聚组函数唯一出现的地方,在where子句中不能使用聚组函数。

     select deptno,sum(sal) from emp where sal>1200 group by deptno having sum(sal)>8500 order by deptno;

          当在gropu by子句中使用having子句时,查询结果中只返回满足having条件的组。在一个sql语句中可以有where子句和having子句。having与where子句类似,均用于设置限定条件
          where子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
          having子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having条件显示特定的组,也可以使用多个分组标准进行分组。

          查询每个部门的每种职位的雇员数

     select deptno,job,count(*) from emp group by deptno,job;

         注意:
        group by不能对别名进行分组排序.举例如下:

         错误SQL:

SELECT to_char(BASICROLL.ROLLDATE,'yyyy-mm') AS YEARDATE,
   DEPTDICT.CNNAME DEPTNAME,
   COUNT(BASICROLL.ID) AS PROJCOUNT
FROM BASICROLL LEFT JOIN DEPTDICT ON BASICROLL.ARRDEPTDICTID=DEPTDICT.ID
WHERE DEPTDICT.CNNAME <> '无'
GROUP BY YEARDATE, DEPTDICT.CNNAME                                      1
ORDER BY YEARDATE DESC                                                  2

 作者原意是将记录按年月,部门进行分组.并倒序排序.但上面的GROUP BY因为用了别名,所以会提示SQL语法错误.如果将上面1,2二句改成:   

GROUP BY BASICROLL.ROLLDATE, DEPTDICT.CNNAME                            3
ORDER BY BASICROLL.ROLLDATE  DESC                                       4

如上3,4二句,又会不合原意,因为BASICROLL.ROLLDATE 包括了年月日.这样分组得出的是按年月日来分组,而不是单纯的年月了.所以正确的SQL应该是这样的.
正确的SQL

SELECT to_char(BASICROLL.ROLLDATE,'yyyy-mm') AS YEARDATE,
   DEPTDICT.CNNAME DEPTNAME,
   COUNT(BASICROLL.ID) AS PROJCOUNT
FROM BASICROLL LEFT JOIN DEPTDICT ON BASICROLL.ARRDEPTDICTID=DEPTDICT.ID
WHERE DEPTDICT.CNNAME <> '无'
GROUP BY to_char(BASICROLL.ROLLDATE,'yyyy-mm'), DEPTDICT.CNNAME                     
ORDER BY to_char(BASICROLL.ROLLDATE,'yyyy-mm') DESC     

以上的注意部分为本人在项目开发过程中经验所得.

六、例子

group by bankno,acctround的意思是找出bankno,acctround两列完全相同的不同行作为一组.那上面的数据就分成两组了,因为acctround不同,而结果需要acctround='1'的组.所以只有一行结果.如果是group by bankno,那么两行会合成一行.它并没有先通过where分析出只有一行符合结果集,再group就只有一行结果了.这里的问题是where和group谁先谁后分析的问题?在这个问题的上下文中,本来的group by bankno就没有任何意义.其实是这样的:

或者是:

select bankno,sum(money) from bankdiff group by bankno;

又或者是:

select bankno,acctround,sum(money) from bankdiff where acctround='1' group by bankno,acctround;

不可能指定acctround又不把它放在groub by中的.


可见这个group by没有意义.因为它有select *,而acctround不一样,就不能只显示一行出来.把select *改成select bankno就只有一行结果了.

抱歉!评论已关闭.