空间管理您的位置: 51Testing软件测试网 » 瑜伽流武者的个人空间 » 日志

学习态度

查询之order by，group by和having的使用（转）

上一篇 / 下一篇 2009-10-31 16:49:55 / 个人分类：测试感慨

查看( 397 ) / 评论( 0 ) / 评分( 0 / 0 )

（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

一、首先讲讲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将被返回。

二、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 ]
   [HAVINGsearch_condition ]
   [ ORDER BY order_expression [ ASC | DESC ] ]

三、理解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 ，一对一成立。

四、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    17100
IBM    17100
W3Schools    17100

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

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

返回结果：

Company    SUM(Amount)
W3Schools    12600
IBM    4500

五、
SQL Group by学习及相关应注意的地方

　　在select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息Oracle按照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
以上的注意部分为本人在项目开发过程中经验所得.

来自：http://gyapollo.yculblog.com/post.1116750.html

六、例子

1> select * from bank_info
2> go
bankno acctround
------ ---------
123456 1
123456 2

(2 rows affected)
1> select * from bank_info where acctround='1' group by bankno order by bankno
2> go
bankno acctround
------ ---------
123456 1
123456 2

(2 rows affected)
1> select * from bank_info where acctround='1' order by bankno
2> go
bankno acctround
------ ---------
123456 1

(1 row affected)
1> select * from bank_info
2> go
bankno acctround
------ ---------
123456 1
123456 2

(2 rows affected)
1> select * from bank_info where acctround='1' group by bankno,acctround order by bankno
2> go
bankno acctround
------ ---------
123456 1

(1 row affected)

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中的．

1> select * from bank_info group by bankno
2> go
bankno acctround
------ ---------
123456 1
123456 2

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

总结：使用group by必需明确你分组的意义，是否有必要这一个列或几个列来确定一个有意义的组，而需要显示的结果行的列是否和group by子句中的列名相冲突．一般不在group by子句中的列名都不会直接在select中显示出来．因为它们要分成一组来或者做些运算再显示．如sum(money)等．

（3）having

一、下面先给出HAVING子句的语法：

SELECT column1, SUM(column2) FROM "list-of-tables"

GROUP BY "column-list"

HAVING "condition";

这个HAVING子句允许你为每一个组指定条件，换句话说，可以根据你指定的条件来选择行。如果你想使用HAVING子句的话，它应该处再GROUP BY子句之后。

下面将以一个例子来解释HAVING子句。假设我们的employee表中包含雇员的name、departmen、salary和age。如果你想为每个部门中每个雇员选择平均工资的话，你可以使用下面的SQL语句：

SELECT dept, avg(salary)

FROM employee

GROUP BY dept;

当然，如果你还想只计算和显示salary大于20000的平均工资的话，你还可以加上HAVING子句：

SELECT dept, avg(salary)

FROM employee

GROUP BY dept

HAVING avg(salary) > 20000;

二、HAVING... 被附加到SQL语句是因为WHER关键字不能被用于聚会函数 (如 SUM)，并且没有HAVING...它将不可能测试结果条件。

语法：

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

这个 "Sales" 表：

Company    Amount
W3Schools    5500
IBM    4500
W3Schools    7100

和这个SQL语句：

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

返回这个结果

Company    SUM(Amount)
W3Schools    12600

相关阅读:

SQL Server的空值处理策略 (51testing, 2009-8-05)
测试数据的思考 (omg, 2009-8-30)
Oracle PL/SQL中函数几个要点 (十期学员, 2009-9-07)
大型应用面临数据库架构危机 (51testing, 2009-9-09)
推荐一本SQL Server2008入门的优秀教程 (rendaoyuan, 2009-9-11)
DataBase Sharding at Netlog 数据库数据切分系统zz (nb_bull, 2009-9-14)
Oracle非法数据库对象引起错误及解决 (fishy, 2009-9-17)
查询之order by，group by和having的使用（转） (丛林猎手, 2009-9-25)
SQL子查询（转） (瑜伽流武者, 2009-10-06)
SQL核心语句使用技巧(转) (瑜伽流武者, 2009-10-31)

查看全部评论

瑜伽流武者

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

访问量: 22294
日志数: 40
图片数: 1
文件数: 1
建立时间: 2009-09-30
更新时间: 2009-11-26

RSS订阅