你真的会玩 SQL 吗?冷落的 Top 和 Apply

转载 2015年11月20日 09:19:43

本文地址:http://blog.csdn.net/shanglianlm/article/details/49942869

本章预先想写一些Top和Apply基本的用法,但好像没什么意义,所以删掉了一些无用的东西,只留下几个示例,以保证系列的完整性。

Top和Apply解决的常见问题,如返回每个雇员的3个最新订单,订单的时间越新优先级就越高,但还需要引入一个决胜属性,以确定时间桢的订单的优先级,如可用id作为决胜属性。这里提供的解决方案比其它方案要简单得多,且执行速度更快。

返回每个雇员的3个最新订单:

SELECT  empid ,
        orderid ,
        custid ,
        orderdate ,
        requireddate
FROM    sales.orders AS o1
WHERE   orderid IN ( SELECT TOP 3
                            orderid
                     FROM   sales.orders AS o2
                     WHERE  o2.empid = o1.empid
                     ORDER BY orderdate DESC ,
                            orderid DESC )

运用APPLY解决:

SELECT  e.empid ,
        a.orderid ,
        a.custid ,
        a.orderdate ,
        a.requireddate
FROM    hr.employees AS e
        CROSS APPLY ( SELECT TOP 3
                                orderid ,
                                custid ,
                                orderdate ,
                                requireddate
                      FROM      sales.orders AS o
                      WHERE     o.empid = e.empid
                      ORDER BY  orderdate DESC ,
                                orderid DESC
                    ) AS a

先扫描employees 获得empid,对每个empid值对orders表查询返回 该雇员的3个最新订单。这里可以返回多个属性。

还有一种解决方案在特定情况下竟然比使用APPLY运算符的方法还要快,使用ROW_NUMBER函数。先为每个订单计算行号,按empid进行分区,并按orderdate desc, orderid desc 顺序排序。然后在外部查询中,只筛选行号小于或等于3的行。

如下:

SELECT  orderid ,
        custid ,
        orderdate ,
        requireddate
FROM    ( SELECT    orderid ,
                    custid ,
                    orderdate ,
                    requireddate ,
                    ROW_NUMBER() OVER ( PARTITION BY empid ORDER BY orderdate DESC , orderid DESC ) AS rownum
          FROM      sales.orders
        ) AS d
WHERE   rownum  3

练习:

从学生表中选取对应班级的前num名学生成绩

--显示结果
/*
bj         xh   name       cj
---------- ---- ---------- -----------
一班         A006 A6         100
一班         A005 A5         99
一班         A001 A1         89
一班         A002 A2         89

二班         B001 B7         100
二班         B001 B6         99
二班         B001 B9         97
二班         B001 B8         90
二班         B001 B5         88
*/
-- 创建测试表
declare @student table(        
---学生表
bj varchar(10),          
-- 班级
xh char(4),                 
-- 学号
name varchar(10),         
-- 姓名   
cj int)                     
-- 成绩

declare @tj table(            
---统计表
bj varchar(10),          
-- 班级
num int)                 
-- 人数  :从学生表中选取对应班级的前num名学生成绩 

set nocount on

-- 添加测试数据
insert @student select '一班' ,'A001','A1',89
insert @student select '一班' ,'A002','A2',89
insert @student select '一班' ,'A003','A3',59
insert @student select '一班' ,'A004','A4',80
insert @student select '一班' ,'A005','A5',99
insert @student select '一班' ,'A006','A6',100
insert @student select '一班' ,'A007','A7',82

insert @student select '二班' ,'B001','B1',19
insert @student select '二班' ,'B001','B2',81
insert @student select '二班' ,'B001','B3',69
insert @student select '二班' ,'B001','B4',86
insert @student select '二班' ,'B001','B5',88
insert @student select '二班' ,'B001','B6',99
insert @student select '二班' ,'B001','B7',100
insert @student select '二班' ,'B001','B8',90
insert @student select '二班' ,'B001','B9',97 

insert @tj select '一班',3
insert @tj select '二班',5

参考SQL:

-- 2005.T-SQL 
select t.bj,s.xh,s.name,s.cj
from @tj t
cross apply (
    SELECT TOP(t.num) 
            with ties       
-- 加 with ties,一班将选出4个人(2个人并列第三名) 
        xh,name,cj 
    from @student
    where t.bj=bj            
-- 加where 功能类似于 inner join ;不加类似于 cross join
    order by cj desc
)s
order by case when t.bj='一班' then 1 else 2 end asc,s.cj desc,s.xh asc    
---排序

本文转自:http://blog.jobbole.com/94919/

你真的会玩SQL吗?Top和Apply

本章预先想写一些Top和Apply基本的用法,但好像没什么意义,所以删掉了一些无用的东西,只留下几个示例,以保证系列的完整性。 Top和Apply解决的常见问题,如返回每个雇员的3个最新订单,订单的...
  • wxw_317
  • wxw_317
  • 2015年11月12日 16:20
  • 369

冷落的 Top 和 Apply

本章预先想写一些Top和Apply基本的用法,但好像没什么意义,所以删掉了一些无用的东西,只留下几个示例,以保证系列的完整性。 Top和Apply解决的常见问题,如返回每个雇员的3个最新订单...
  • u013063153
  • u013063153
  • 2015年11月21日 23:33
  • 183

你真的会玩SQL吗?内连接、外连接

大多数人一般写多表查询会这样写select * from tbA ,tbB  没有用到JOIN关键字,太Low了,官网标准建议是用JOIN明确表间的关系,下面具体来讲。 连接类型: ...
  • GarfieldEr007
  • GarfieldEr007
  • 2017年04月06日 22:39
  • 285

你真的会php吗?

今天来讨论一道php代码审计的题,这是一道实验吧的题,链接: http://ctf5.shiyanbar.com/web/PHP/index.php 。 首先我们点开链接,并且查看源代码, 发...
  • JBlock
  • JBlock
  • 2017年12月07日 20:31
  • 791

细思极恐-你真的会写java吗?

细思极恐-你真的会写java吗? 导语 自2013年毕业后,今年已经是我工作的第4个年头了,总在做java相关的工作,终于有时间坐下来,写一篇关于java写法的一篇文章,来探讨一下如果你真的是一个...
  • sinat_32366329
  • sinat_32366329
  • 2017年03月12日 15:18
  • 911

你真的会玩SQL吗?实用函数方法汇总

实用函数方法 由于有些知识很少被用到,但真需要用时却忘记了又焦头烂额的到处找。 现在将这些‘冷门“却有效的小知识贡献出来,以备不时之需。 存储过程中的 '''' 相当于数...
  • u013063153
  • u013063153
  • 2015年11月19日 20:44
  • 284

你真的懂 Java 的内存管理和引用类型吗?

前言对于 Java 程序员来说,在 Java 虚拟机自动内存管理机制的帮助下,不再需要为每一个 new 操作去写对应的 delete/free 代码,不容易出现内存泄露和内存溢出的问题。不过,也正是因...
  • c6E5UlI1N
  • c6E5UlI1N
  • 2018年01月30日 00:00
  • 75

你们了解真正的黑客吗?

今天看了《黑客与画家》,真正颠覆了以前的想法,真的印证了那句话“无知真可怕”黑客与画家...
  • zzh_receive
  • zzh_receive
  • 2016年12月02日 11:54
  • 1630

你真的会用 CocoaPods 吗?

CocoaPods 可以说是 iOS 开发应用最广泛的包管理工具,本篇文章主要介绍 CocoaPods 的第三方库是怎样从网络集成到我们本地的项目当中,也是制作私有库、开源库和 iOS 项目组件化的一...
  • qq_34047841
  • qq_34047841
  • 2017年11月07日 14:10
  • 87

SQL关于apply的两种形式cross apply和outer apply

SQL Server数据库操作中,在2005以上的版本新增加了一个APPLY表运算符的功能。新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行。它不像JOIN那样先计算那个表表达式都可以,...
  • felixfeng
  • felixfeng
  • 2015年12月23日 10:32
  • 3296
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:你真的会玩 SQL 吗?冷落的 Top 和 Apply
举报原因:
原因补充:

(最多只允许输入30个字)