日期:2014-05-16 浏览次数:20612 次
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-12 12:26:33
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[SC]
if object_id('[SC]') is not null drop table [SC]
go
create table [SC]([S#] int,[Sname] nvarchar(2),[C#] nvarchar(4),[Score] int)
insert [SC]
select 1001,'A',N'语文',98 union all
select 1002,'B',N'语文',78 union all
select 1003,'C',N'语文',78 union all
select 1004,'D',N'语文',88 union all
select 1001,'A',N'数学',88 union all
select 1002,'B',N'数学',68 union all
select 1003,'C',N'数学',78 union all
select 1004,'D',N'数学',98
--------------生成数据--------------------------
SELECT s#,SNAME,C#,SCORE
FROM (
select *,ROW_NUMBER()OVER(PARTITION BY C# ORDER BY SCORE DESC)ID
from [SC])A
WHERE ID IN (1,2)
----------------结果----------------------------
/*
s# SNAME C# SCORE
----------- ----- ---- -----------
1004 D 数学 98
1001 A 数学 88
1001 A 语文 98
1004 D 语文 88
*/