学习 APPLY 和 TOP 用法

-- 学习 APPLY 和 TOP 用法

-----------------------------------------------------------------------
-- SQL 版本：
-------------
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
--    Oct 14 2005 00:33:37
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
-----------------------------------------------------------------------

--> 创建测试表
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

-- 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    ---排序


/*
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
*/