2137人阅读
8.11. 数据分组小计
利用GROUP BY子句，可以根据一个或多个组的值将查询中的数据记录分组。
1.&&&&&&& 计算出员工“飞狐工作室”表中，各个部门的薪资最高值、薪资最小值、薪资平均值及人数。
/* 档案名称: Demo956.sql */
USE NorthwindSQL
COUNT(*) AS 部门员工人数,
MAX(目前薪资) AS
部门最高薪资,
MIN(目前薪资) AS
部门最低薪资,
AVG(目前薪资) AS
部门平均薪资
飞狐工作室
2.&&&&&&& 计算出每一位客户的采购次数和采购总金额。
/* 档案名称: Demo957.sql */
USE NorthwindSQL
SELECT a.公司名称,
COUNT(DISTINCT b.订单号码)
AS 采购次数,
SUM(c.单价 * c.数量 * (1 - c.折扣))
AS 采购总金额
INNER JOIN 订货主档 b
INNER JOIN
订货明细 c
ON b.订单号码 = c.订单号码
ON a.客户编号 = b.客户编号
BY a.公司名称
3.&&&&&&& 查询出每一位业务人员的销售总数。
/* 档案名称: Demo958.sql */
USE NorthwindSQL
SELECT a.员工编号,a.姓名,
&&&&&& SUM(c.数量) AS
&&&&&& INNER JOIN
订货主档 b
&&&&&& INNER JOIN
订货明细 c
&&&&&& ON b.订单号码 = c.订单号码
&&&&&& ON a.员工编号 = b.员工编号
BY a.员工编号,a.姓名
4.&&&&&&& 查询出每一位业务人员的销售总数，同时列出那些比他或她销售还多的业务员数及平均销售数目。
/* 档案名称: Demo959.sql */
EXEC sp_dboption 'NorthwindSQL','select into/bulkcopy','TRUE'&
--设为true能加快select
into的处理速度
USE NorthwindSQL
--首先，生成一张aMount表，包含每一位业务人员的销售总数
SELECT a.员工编号,a.姓名,SUM(c.数量)
AS 销售总数 INTO aMount
&&&&&& INNER JOIN
订货主档 b
&&&&&& INNER JOIN
订货明细 c
&&&&&& ON b.订单号码 = c.订单号码
&&&&&& ON a.员工编号 = b.员工编号
BY a.员工编号,a.姓名
SELECT a.员工编号,
a.销售总数,
&&&&&& COUNT(b.员工编号) AS
业绩比此人好之业务员的人数,
&&&&&& AVG(b.销售总数) AS
业绩比此人好之业务员的平均销售总数
& FROM aMount a, aMount b
& WHERE a.销售总数 & b.销售总数
BY a.员工编号,a.姓名,a.销售总数
EXEC sp_dboption 'NorthwindSQL','select into/bulkcopy','FALSE'
DROP TABLE aMount
5.&&&&&&& 为“飞狐工作室”表的各部门计算出“女性未婚”、“女性已婚”、“男性未婚”、“男性已婚”员工的最低薪资、最高薪资和平均薪资。
/* 档案名称: Demo960.sql */
USE NorthwindSQL
--本例中，性别、婚姻状况是bit数据类型，SQL
Server不允许使用bit类型的字
--段作为分组，这里变通了一下。
性别 = CASE 性别
&&&&&&&&&&&&&& WHEN 0 THEN '女'
&&&&&&&&&&&&&& WHEN 1 THEN '男'
&&&&&&&&&&&& END,
婚姻状况 = CASE 婚姻状况
&&&&&&&&&&&&&&&&&& WHEN 0 THEN '未婚'
&&&&&&&&&&&&&&&&&& WHEN 1 THEN '已婚'
&&&&&&&&&&&&&&&& END,&&&&&&&&&
最低薪资 = MIN(目前薪资),
最高薪资 = MAX(目前薪资),
平均薪资 = AVG(目前薪资)
飞狐工作室
&&&&&&&&&& CASE
&&&&&&&&&&&& WHEN 0 THEN '女'
&&&&&&&&&&&& WHEN 1 THEN '男'
&&&&&&&&&& END,
&&&&&&&&&& CASE
&&&&&&&&&&&& WHEN 0 THEN '未婚'
&&&&&&&&&&&& WHEN 1 THEN '已婚'
&&&&&&&&&& END
8.12. HAVING 子句的使用
HAVING搜索条件子句通常与GROUP BY子句合用，用来设置查询结果中“组”所需符合的条件。在这里我们自然会想为什么不用where因为where
不可以用聚合函数
即对要列出哪几个分组进行了限定。
1.&&&&&&& 计算出员工“飞狐工作室”表中，部门平均薪资大于<span style="color:#000的各个部门的薪资最高&#20540;、薪资最小&#20540;、薪资平均&#20540;及人数。
/* 档案名称: Demo961.sql */
USE NorthwindSQL
SELECT部门,
&&&&&& AVG(目前薪资) AS
部门的平均薪资,
&&&&&& COUNT(*) AS
各部门之人数,
&&&&&& MAX(目前薪资) AS
部门中的最高薪,
&&&&&& MIN(目前薪资) AS
部门中的最低薪
飞狐工作室
AVG(目前薪资) & 30000
2.&&&&&&& 查询出“飞狐工作室”表中，有哪些员工的姓名是跟别人相同的，而且同样采用某一姓名者又有多少人。
/* 档案名称: Demo962.sql */
USE NorthwindSQL
姓名 AS 重复的姓名,
&&&&&& COUNT(*) AS
重复的人数
飞狐工作室
&&Having COUNT(*) & 1
3.&&&&&&& 计算出订单超过10张的各家客户的订单数目，及其采购总金额。
/* 档案名称: Demo963.sql */
USE NorthwindSQL
a.公司名称,
&&&&&& COUNT(DISTINCT b.订单号码) AS
&&&&&& SUM(c.单价 * c.数量 * (1 - c.折扣)) AS
采购总金额
INNER JOIN 订货主档 b
INNER JOIN 订货明细 c
ON b.订单号码 = c.订单号码
ON a.客户编号 = b.客户编号
BY a.公司名称
& HAVING COUNT(DISTINCT b.订单号码)
8.13. ORDER BY 子句的使用
利用ORDER BY子句，可以根据一个或多个字段的数据排序查询出的结果。
1.&&&&&&& 按照薪资所得的高低顺序，列出“飞狐工作室”表中所有员工的姓名、性别和薪资。
/* 档案名称: Demo964.sql */
USE NorthwindSQL
性别 = CASE 性别
&&&&&&&&&&&&&& WHEN 0 THEN '女'
&&&&&&&&&&&&&& WHEN 1 THEN '男'
&&&&&&&&&&&& END,
飞狐工作室
BY 目前薪资
DESC& //可用ORDER BY
2.&&&&&&& 将“飞狐工作室”表中各部门的平均薪资由低到高列出来。
/* 档案名称: Demo965.sql */
USE NorthwindSQL
&&&&&& AVG(目前薪资) AS
飞狐工作室
3.&&&&&&& 列出“飞狐工作室”表中所有员工的隶属部门、员工姓名、性别和薪资，按部门、性别（降序）、目前薪资（降序）来排列。
/* 档案名称: Demo966.sql */
USE NorthwindSQL
性别 = CASE 性别
&&&&&&&&&&&&&& WHEN 0 THEN '女'
&&&&&&&&&&&&&& WHEN 1 THEN '男'
&&&&&&&&&&&& END,
飞狐工作室
BY 部门,性别
DESC,目前薪资
4.&&&&&&& 计算出每一位客户的采购次数和采购总金额。先按照采购次数由多到少排列，而采购次数相同者，则再按照采购总金额由低到高排列。
/* 档案名称: Demo967.sql */
USE NorthwindSQL
SELECT a.公司名称,
&&&&&& COUNT(DISTINCT b.订单号码) AS
&&&&&& SUM(c.单价 * c.数量 * (1 - c.折扣)) AS
采购总金额
&&&&&& INNER JOIN
订货主档 b
&&&&& &INNER JOIN
订货明细 c
&&&&&& ON b.订单号码 = c.订单号码
&&&&&& ON a.客户编号 = b.客户编号
BY a.公司名称
BY 2 DESC, 3
8.14. 查询名列前茅或落后者
先利用ORDER BY排序，然后利用关键字TOP n [PERCENT]来取出从头算起的前几名或从头算起的多少百分比。
1.&&&&&&& 查询出“飞狐工作室”中，薪资最低员工的姓名及其薪资。
/* 档案名称: Demo968.sql */
USE NorthwindSQL
姓名 AS 薪资最低之员工姓名,
飞狐工作室
BY 目前薪资
2.&&&&&&& 查询出“飞狐工作室”中，薪资最高员工的姓名及其薪资。
3.&&&&&&& 查询出“飞狐工作室”中，薪资最高的前10名员工的姓名及其薪资。
4.&&&&&&& 查询出“飞狐工作室”中，薪资倒数前10名员工的姓名及其薪资。
5.&&&&&&& 查询出“飞狐工作室”中，部门的平均薪资最高的前3个部门。
/* 档案名称: Demo972.sql */
USE NorthwindSQL
部门 AS 平均薪资最高的前三个部门,
&&&&&& AVG(目前薪资) AS
飞狐工作室
6.&&&&&&& 查询出“飞狐工作室”中，目前薪资最高的前10％的员工。
7.&&&&&&& 查询出销售业绩最佳的前3名业务员。
/* 档案名称: Demo974.sql */
USE NorthwindSQL
SELECTTOP 3
&&&&&& a.员工编号,
&&&&&& a.姓名 AS
业务员姓名,
&&&&&& SUM(c.单价 * c.数量*(1 - c.折扣)) AS
销售总金额
&&&&&& INNER JOIN
订货主档 b
&&&&&& INNER JOIN
订货明细 c
&&&&&& ON b.订单号码 = c.订单号码
&&&&&& ON a.员工编号 = b.员工编号
BY a.员工编号,a.姓名
8.&&&&&&& 查询出在1996年采购总金额最高的客户。
/* 档案名称: Demo975.sql */
USE NorthwindSQL
&&&&&& a.公司名称,
&&&&&& COUNT(DISTINCT b.订单号码) AS
&&&&&& SUM(c.单价 * c.数量 * (1 - c.折扣)) AS
采购总金额
客户 a INNER JOIN 订货主档 b
&&&&&&&&&&&& INNER JOIN
订货明细 c
&&&&&&&&&&&& ON b.订单号码 = c.订单号码
&&&&&&&&&&&& ON a.客户编号 = b.客户编号
YEAR(b.订单日期) = 1996
BY a.公司名称
8.15. 活用子查询(SubQuery)
n&&&&& 子查询：就是包含在某一个SELECT、INSERT、UPDATE、DELETE命令中的SELECT查询。
n&&&&& 在SELECT、INSERT、UPDATE、DELETE命令中凡是允许是一个表达式的地方均可以包含子查询。
n&&&&& 子查询甚至可以再包含一个子查询。
如：蓝色部分为子查询
/* 档案名称: Demo976.sql */
USE NorthwindSQL
订货主档.订单号码,订货主档.订单日期,
&& (SELECT MAX(订货明细.单价)
FROM 订货明细
WHERE 订货主档.订单号码 =
订货明细.订单号码)
AS 最高单价
1.&&&&&&& 本例以子查询的返回&#20540;作为WHERE子句的条件&#20540;，以便查询哪些员工的薪资和公司中最低薪资人员相同。
/* 档案名称: Demo977.sql */
USE NorthwindSQL
SELECT a.姓名,
&&&&&& a.目前薪资
FROM 飞狐工作室 a
WHERE a.目前薪资 =
(SELECT MIN(b.目前薪资) FROM
飞狐工作室 b)
2.&&&&&&& 本例通过子查询得知有哪些员工的薪资比公司的平均薪资还高出50％
/* 档案名称: Demo978.sql */
USE NorthwindSQL
SELECT a.姓名,
&&&&&& a.目前薪资
FROM 飞狐工作室 a
WHERE a.目前薪资 &
&&& &&&(SELECT AVG(b.目前薪资)
* 1.50 FROM 飞狐工作室 b)
ORDER BY a.目前薪资
3.&&&&&&& 本例通过子查询查询出人数比业务部门还多的各部门员工的平均薪资。
/* 档案名称: Demo982.sql */
USE NorthwindSQL
部门,COUNT(*) AS 部门人数,AVG(目前薪资) AS
飞狐工作室
COUNT(*) &
(SELECT COUNT(*) FROM 飞狐工作室 WHERE
部门 = '业务部')
n&&&&& 子查询必须包含在一对小括号内。
“EXISTS”子查询的测试
n&&&&& 使用EXISTS关键字，可检查是否至少有一条数据记录存在于子查询的查询结果中。
n&&&&& 在子查询的SELECT语句选择列表中通常使用通配符星号*。
1.&&&&&&& 查询出哪些公司并没有采购。
/* 档案名称: Demo980.sql */
USE NorthwindSQL
客户.客户编号,客户.公司名称
NOT EXISTS
FROM 订货主档
WHERE 订货主档.客户编号 =
客户.客户编号)
“IN”子查询的测试
1.&&&&&&& 查询出哪些公司并没有采购。
/* 档案名称: Demo983.sql */
USE NorthwindSQL
a.公司名称
& WHERE a.客户编号 NOT IN
&&&&&&&&&&&&&&
&(SELECT b.客户编号 FROM
订货主档 b)
2.&&&&&&& 查询出哪些公司曾经采购过。（代码略）
附注：其实用EXISTS和IN可以达到相同的效果。
8.16. &本章命令一缆表
1．一条简单的SQL语句
Select 字段名/常量/SQL表达式 &&&&&&&&――select 子句
From 表名列表&&&&&&&&&&&&&&&&&&&&& &――from 子句
2．查询结果
与数据库中的表一样，SQL查询的结果总是一个数据表。它也许只有一个字段、也许只有一条记录、也许只有一条记录一个字段的小表，当然也可以是一张空表。
SQL查询总是生成一个数据表的事实是非常重要的，它意味着查询结果可以作为一个表存回到数据库中，也也意味着两个类&#20284;查询结果可以组合以形成一个较大的查询结果表。最后，它意味着查询结果本身可以是进一步查询的目标。因此，关系数据库的表&#26684;式结构对SQL的关系查询功能具有协同增强的关系。表可以被查询，查询可以生成表。
3．简单查询
Select 子句命名必需的字段，from 子句命名包含它们的表。选择所有的字段（select*）,
* 星号作为“所有字段”的缩写。
4．重复的记录（distint）
Distinct用于清除重复的记录。
5．选择记录（where）
使用where来指定你想要检索的记录。
本质上，对于表中的每一条记录，搜索条件起到过滤器的作用。
6．搜索条件
比较测试（＝，&,&,&,&=,&,&=）：
把一个表达式的&#20540;和另一个表达式得&#20540;进行比较。
在SQL的3&#20540;逻辑下，一个搜索条件可以产生True，False或Null&#20540;。只有其搜索条件产生True的那些记录才被包括在查询结果中。
范围测试（between……and）：
测试一个表达式的&#20540;是否是在一个指定的&#20540;的范围内。
三个表达式的数据类型必须是可比较的。
组成员测试（in/not in）：
检查一个表达式的&#20540;是否匹配一组&#20540;中的一个。
模式匹配测试(Like)：
检查包含字符串数据的字段的&#20540;是否匹配一个指定的模式。
课件中说的比较详细，可参考。
Null&#20540;测试：
检查是否一个字段有一个Null（未知）&#20540;。
字段名&&& is
NULL&#20540;测试（is Null）语法图
复合搜索条件（AND ,OR和NOT）
7．排序查询结果(order by 子句)
建议使用段号select sol表达式………
8．多表查询（连接）
课件详细。
9．汇总查询
SUM() 计算字段的总和。
AVG() 计算字段的平均&#20540;。
MIN() 查找字段中的最小&#20540;。
MAX()查找字段中的最大&#20540;。
COUNT()计算字段中&#20540;的数目。
COUNT(*)计算查询结果的记录数。
DISTINCT /ALL 消除重复记录。
SELECT COUNT(DISTINCT 客户编号) AS 客户数目
& FROM 订货主档
& WHERE 订单日期 BETWEEN '08/01/1996' AND '08/31/1996'
10．分组查询（group by子句）
& GROUP BY& 字段1，字段2，字段3
Select 分组字段/聚合函数&&&&&& ――select 后面只能是。
11．分组搜索条件（having by 子句）
Having by 后面的条件字段可以是那些字段？
12．子查询
Select city
From offices
Where target &(select sum(quota)
From salesaeps
Where rep_office=office)
子查询搜索条件
子查询比较测试（＝，&=,&,&=,&,&&）
组成员测试（in）
& Where 一个子段&
in (select 两个字段)
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：229471次
积分：2015
积分：2015
排名：第16810名
原创：25篇
转载：52篇
(1)(1)(1)(1)(2)(2)(1)(2)(2)(2)(3)(3)(5)(4)(5)(1)(2)(3)(2)(6)(4)(2)(1)(1)(1)(6)(2)(5)(12)求教一个递归查询+聚合的sql语句，求高手哈表A存放部门人数，现在想统计各个部门及其下级部门的人数select level as alevel,A.depart_code,A.depart_name,A.pnum&
& from A & start with A.depart_code='d001' connect by prior A.depart_code=A.super_depart_code查询结果：1
销售部本部
销售部分部1
销售部分部2
分部管理处
分部人事办
3现在想实现查询结果向下钻取展示，如：部门，人数销售部本部合计，27 &
销售部本部，8 &
销售部分部1合计，7 &
销售部分部1，7 &
销售部分部2合计,12 &
销售部分部2，5 &
分部管理处，4 &
分部人事办，3
回答1：类似这样的语句select super_dept_code,dept_name,sum(pnum),grouping(dept_name)from (select a.dept_code,a.dept_name ,a.pnum,a.super_dept_codefrom a03 astart with a.dept_code='d001' connect by prior a.dept_code=a.super_dept_code)group by rollup(super_dept_code,dept_name)
　　SQL code　　create table t1
name varchar2(20),
dno number,
for i in 42..51 loop
insert into t1 values(i,'test',10007);
create table t2
name varchar2(20),
dno number,
insert into t2 values(1,'销售部',10005,0);
insert into t2 values(2,'销售分部',10015,1);
insert into t2 values(3,'销售分部',10025,1);
insert into t2 values(4,'人事部',10006,0);
insert into t2 values(5,'管理办',10007,0);
select name,a1.dno,a1.人员总数,a2.合计 from (
select t2.name,t2.dno, count(t2.dno) 人员总数,t2.type
from t1,t2 where t1.dno=t2.dno group by t2.name,t2.dno,t2.type) a1,
(select t2.type,count(t2.type) 合计 from t1,t2 where t1.dno=t2.dno group by t2.type) a2 where a1.type=a2.type}