求大神帮忙解决 SQL

表SC 有字段S#学生ID，Sname 学生姓名，C#学科,Score 分数  取各科成绩排前2的学生信息
这个该怎么写？
假设表数据如下


需要得到如下结果

4 个解决方案

#1

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

#2

这个在2005开始可用，看你的截图应该是2005或以上版本

#3

并列第二名怎么处理？

#4

谢谢！我是2008的