第六章 高级数据查询
一般数据查询功能扩展
使用TOP限制 结果集
TOP n [percent] [WITH TIES]
- TOP n : 取查询结果的前n行数据
- TOP n percent : 取查询结果的前n%行数据
- WITH TIES : 表示包括最后一行取值并列的结果
注意：TOP要和ORDER BY一起使用才有意义。
例：
/**
* 查询单价最高的前三种商品的商品名、商品类别和单价，包括并列的情况
*/
select top 3 with ties GoodsName,GoodsClassName,SaleUnitPrice
from Table_Goods a join Table_GoodsClass b
on a.GoodsClassID = b.GoodsClassID
order by SaleUnitPrice desc
使用CASE函数
简单CASE函数
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2
…
WHEN 简单表达式n THEN 结果表达式n
[ELSE 结果表达式n+1]
END
搜索CASE函数
CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2
…
WHEN 布尔表达式n THEN 结果表达式n
[ELSE 结果表达式n+1]
END
注意：若没有ELSE语句，则返回NULL值
例：
/**
* 对家用电器类商品进行分类显示。如果商品单价高于3000元，则显示“高档商品”；如果单价在1000-3000
* 元，则显示“中档商品”；如果单价低于1000元，则显示“低档商品”。
*/
select a.GoodsID,商品销售类别 = case
when count(b.GoodsID)>10 then '热门商品'
when count(b.GoodsID) between 5 and 10 then '一般商品'
when count(b.GoodsID) between 1 and 4 then '难销商品'
else '滞销商品'
from Table_Goods a left join Table_SaleBillDetail b
on a.GoodsID = b.GoodsID
group by a.GoodsID
将查询结果保存到新表中
SELECT 查询列表序列 INTO <新表名>
FROM 数据源
…
新表可以是永久表，也可以是临时表，临时表又分为局部临时表（表名前加一个#），和全局临时表（表名前加两个#）。
例：
select CustomerID,CName,Sex,CardID into #HD_Customer
from Table_Customer
查询结果的并、交、差运算
并运算
SELECT 语句1
UNION [ALL]
SELECT 语句2
UNION [ALL]
…
SELECT 语句n
ALL表示在结果集中包含所有查询语句产生的全部记录，包括重复的记录，如果没有指定ALL，则系统默认是删除合并后结果集中的重复记录。
注意：
- 使用UNION操作的查询，其SELECT列表中列的个数必须相同，而且对应列的语义应该相同。
- 相对应的属性列的数据类型必须是兼容的
- 合并后的结果集采用第一个SELECT语句的列标题
- 如果要对查询的结果进行排序，则ORDER BY子句应该写在最后一个查询语句之后，且排序的依据列应该是第一个查询语句中出现的列名。
例：
# 查询姓李和姓王的客户的姓名和电话，并将查询结果按电话升序排序
select Name,Tel from Table_Customer where Name like '李%'
union
select Name,Tel from Table_Customer where Name like '王%'
order by Tel asc
交运算
SELECT 语句1
INTERSECT
SELECT 语句2
INTERSECT
…
SELECT 语句n
例：
# 查询顾客朱时七和王达力所购买的相同商品，列出商品名和商品销售单价
select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID=G.GoodsID
join Table_SaleBill S on S.SaleBillID=SD.SaleBillID
join Table_Customer C on C.CardID=S.CardID
where Name='朱时七'
intersect
select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID=G.GoodsID
join Table_SaleBill S on S.SaleBillID=SD.SaleBillID
join Table_Customer C on C.CardID=S.CardID
where Name='王达力'
差运算
SELECT 语句1
EXCEPT
SELECT 语句2
EXCEPT
…
SELECT 语句n
例：
# 查询顾客王达力购买了而朱时七没有购买的商品，列出商品名和商品销售单价
select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID=G.GoodsID
join Table_SaleBill S on S.SaleBillID=SD.SaleBillID
join Table_Customer C on C.CardID=S.CardID
where Name='王达力'
except
select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID=G.GoodsID
join Table_SaleBill S on S.SaleBillID=SD.SaleBillID
join Table_Customer C on C.CardID=S.CardID
where Name='朱时七'
相关子查询
- WHERE 表达式 [NOT] IN （子查询）
- WHERE 表达式 比较运算符 （子查询）
- WHERE [NOT] EXISTS （子查询）
# 查询与王晓在同一个地址（区相同）的顾客的姓名和所在地址
select Cname,Address from Table_Customer
where Address in(
select Address from Table_Customer where Cname='王晓')
and Cname!='王晓'
# 查询单价最高的商品名称和单价
select GoodsName,SaleUnitPrice from Table_Goods a
where SaleUnitPrice=(select max(SaleUnitPrice) from Table_Goods)
# 查询购买了单价高于2000元商品的顾客的会员卡号
select distinct CardID from Table_SaleBill
where exists(
select * from Table_SaleBillDetail
where SaleBillID=Table_SaleBill.SaleBillID and UnitPrice>2000
)
其它形式的子查询
替代表达式的子查询
替代表达式的子查询是指在select语句的选择列表中嵌入一个只返回一个标量值的select语句，这个查询语句通常都是通过一个聚合函数来返回一个单值。
# 查询G001顾客的姓名、地址以及该顾客购买商品的总次数
select CName,Address,
(select count(*) from Table_SaleBill a join Table_Customer b on a.CardID=b.CardID where CustomerID='G001')
as TotalTimes from Table_Customer
where CustomerID='G001'
派生表
派生表是将子查询作为一个表来处理，这个由子查询产生的新表就被称为“派生表”。可以在查询语句中用派生表来建立与其他表的连接关系。
# 查询至少买了G001和G002两种商品的顾客号和顾客名
select CustomerID,CName
from (select * from Table_SaleBill a
join Table_SaleBillDetail b
on a.SaleBillID=b.SaleBillID
where GoodsID='G001') as T1
join (select * from Table_SaleBill a
join Table_SaleBillDetail b
on a.SaleBillID=b.SaleBillID
where GoodsID='G002') as T2
on T1.CardId=T2.CardID
join Table_Customer c on c.CardID=T1.CardID
其它的查询功能
开窗函数
在SQL Serve中，一组行被称为一个窗口，开窗函数是指可以用于”分区“或”分组“计算的函数。这些函数结合OVER子句对组内的数据进行编号，并进行求和、计算平均值等统计。开窗函数可以分别应用于每个分区，把每个分区看成是一个窗口，并为每个分区进行计算。开窗函数必须放在OVER子句前边。
开窗函数是在ISO SQL标准中定义的。SQL Server提供了两种开窗函数：排名开窗函数和聚合开窗函数。
将OVER子句与聚合函数结合使用
OVER{[PARTITION BY value_expression,…[n]]}
- PARTITION BY : 将结果集划分成多个分区。开窗函数分别应用于每个分区，并为每个分区计算函数值。
- value_expression : 指定对行集进行分区所依据的列，该列必须是在FROM子句中生成的列，而且不能引用选择列表中的表达式或别名。value_expression可以是列表达式、替代表达式的子查询、标量函数或用户定义的变量。
例：
select Cno,CName,Semester,Credit,
sum(Credit) over(partition by Semeter) as 'Total',
avg(Credit) over(partition by Semeter) as 'Avg',
min(Credit) over(partition by Semeter) as 'Min',
max(Credit) over(partition by Semeter) as 'Max'
from Course
将OVER子句与排名函数一起使用
RANK() OVER([<partition_by_clause>,…[n]]<order_by_clause>)
取值不一定连续
例：
# 查询订单号、产品号、订购数量以及每个商品在每个订单中的订购数量排名
select OrderID,ProductID,OrderQty,
rank() over (partition by OrderID order by OrderQty desc) as rank
from OrderDetail
order by OrderID
DENSE_RANK() OVER([<partition_by_clause>,…[n]]<order_by_clause>)
取值连续
例：
# 查询订单号、产品号、订购数量以及每个商品在每个订单中的订购数量排名
select OrderID,ProductID,OrderQty,
dense_rank() over (partition by OrderID order by OrderQty desc) as rank
from OrderDetail
order by OrderID
NTILE(integer_expression) OVER ([<partition_by_clause>]<order_by_clause>)
将有序分区中的行划分到指定数目的组中，每个组有一个编号，从1开始。对于每一行，函数将返回此行所属的组的编号。
例：
# 将每个订单中的数据按订购数量排序降序，并将每个订单的数据划分到3个组中
select OrderID,ProductID,OrderQty,
ntile(3) over(partition by OrderID order by OrderQty DESC) as ThreeGroups
from OrderDetail
ROW_NUMBER() OVER([<partition_by_clause>]<order_by_clause>)
函数返回结果集中每个分区内行的序列号，每个分区的第一行从1开始
例：
# 查询“电冰箱”类商品的商品名、销售单价以及该商品在该类商品中的价格排名
select GoodsName,SaleUnitPrice,
row_number() over (order by SaleUnitPrice desc) as 'Number'
from Table_GoodsClass C join Table_Goods G
on C.GoodsClassID=G.GoodsClassID
where GoodsClassName='电冰箱'
公用表表达式
将查询的结果集指定一个临时命名的名字，这些命名的结果集就称为公用表表达式。
WITH expression_name [(column_name[,…n])] AS (SELECT 语句)
# 定义一个统计每个会员的购买商品总次数的CTE，并利用该CTE查询会员卡号和购买商品次数
with BuyCount(CardID,Counts) as (
select CardID,count(*) from Table_SaleBill
group by CardID)
select CardID ,Counts from BuyCount
where Counts>10
order by Counts
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
