学习 APPLY 和 TOP 用法

最新推荐文章于 2024-11-17 22:56:37 发布

原创最新推荐文章于 2024-11-17 22:56:37 发布 · 377 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #microsoft #sql server #join #table #测试

学习日记专栏收录该内容

27 篇文章

订阅专栏

本文通过一个具体的例子展示了如何使用SQL Server中的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
*/