请教如何实现下面查询？

Question

如表月份 姓名 数学 语文 英语
1月 小明 86 92 70
1月 小王 72 58 90
1月 小张 86 88 72
2月 小明 85 90 76
2月 小王 75 70 88
2月 小张 93 87 73
3月 小明 75 91 90
3月 小王 77 63 89

希望得到下表结果：

月份    姓名  数学最高成绩  姓名  语文最高成绩 姓名 英语最高成绩
1月 小明   86     小明 92 小王  90
1月 小张   86     小明 92 小王  90
2月 小张   93     小明 90 小王  88
3月 小王   77     小明 91 小明  90一个月一次考试，取得每个月份的最高成绩的列表，如何实现？要是想取前两名该怎么实现？

Answer 1

每门课的前两名应该是如下才是啊？1月 小明 86 小明 92 小王 90
1月 小张 86 小张 88 小张 72
2月 小张 93 小明 90 小王 88
2月 小明 85 小张 87 小明 76
3月 小王 77 小明 91 小明 90
3月 小明 75 小王 63 小王 89--演示环境
CREATE TABLE TEST(MON VARCHAR(20),NAME VARCHAR(20),SX INT,YW INT,YY INT)
INSERT INTO TEST(MON,NAME,SX,YW,YY)
SELECT '1月','小明',86,92,70 
UNION ALL SELECT '1月','小王',72,58,90 
UNION ALL SELECT '1月',',小张',86,88,72
UNION ALL SELECT '2月','小明',85,90,76 
UNION ALL SELECT '2月','小王',75,70,88
UNION ALL SELECT '2月','小张',93,87,73 
UNION ALL SELECT '3月','小明',75,91,90 
UNION ALL SELECT '3月','小王',77,63,89
--查询 MS-SQL SERVER2005及以上版本
SELECT * FROM TEST
------------------------------------------
;WITH
DD_1 AS(
SELECT  T1.MON,T1.NAME,T1.SX,T1.YW,T1.YY,NB1
FROM (SELECT MON,NAME,SX,NULL AS YW,NULL AS YY,ROW_NUMBER()OVER(PARTITION BY MON ORDER BY SX DESC)NB1
      FROM TEST)T1
WHERE T1.NB1<3 
),
DD_2 AS(
SELECT  T2.MON,T2.NAME,T2.YW,NB2
FROM (SELECT MON,NAME,YW,ROW_NUMBER()OVER(PARTITION BY MON ORDER BY YW DESC)NB2
      FROM TEST)T2
WHERE T2.NB2<3 
),
DD_3 AS(
SELECT  T3.MON,T3.NAME,T3.YY,NB3
FROM (SELECT MON,NAME,YY,ROW_NUMBER()OVER(PARTITION BY MON ORDER BY YY DESC)NB3
      FROM TEST)T3
WHERE T3.NB3<3 
)
SELECT DD_1.MON 月份,DD_1.NAME 姓名,DD_1.SX 数学成绩前两名,DD_2.NAME 姓名
       ,ISNULL(DD_1.YW,DD_2.YW) 语文成绩前两名,DD_3.NAME 姓名,
       ISNULL(DD_1.YW,DD_3.YY)  英语成绩前两名
FROM DD_1 
     LEFT JOIN DD_2 
     ON DD_1.MON=DD_2.MON
        AND DD_1.NB1=DD_2.NB2
     LEFT JOIN DD_3 
     ON DD_1.MON=DD_3.MON
        AND DD_1.NB1=DD_3.NB3
/*结果:
月份                   姓名                   数学成绩前两名     姓名                   语文成绩前两名     姓名                   英语成绩前两名
-------------------- -------------------- ----------- -------------------- ----------- -------------------- -----------
1月                   小明                   86          小明                   92          小王                   90
1月                   ,小张                  86          ,小张                  88          ,小张                  72
2月                   小张                   93          小明                   90          小王                   88
2月                   小明                   85          小张                   87          小明                   76
3月                   小王                   77          小明                   91          小明                   90
3月                   小明                   75          小王                   63          小王                   89(6 行受影响)
*/---删除演示环境
DROP TABLE TEST

Answer 2

------MS-SQL SERVER2000SELECT DD_1.MON 月份,DD_1.NAME 姓名,DD_1.SX 数学成绩前两名,DD_2.NAME 姓名
       ,ISNULL(DD_1.YW,DD_2.YW) 语文成绩前两名,DD_3.NAME 姓名,
       ISNULL(DD_1.YW,DD_3.YY)  英语成绩前两名
FROM 
    (
SELECT  T1.MON,T1.NAME,T1.SX,T1.YW,T1.YY,NB1
FROM (SELECT MON,NAME,SX,NULL AS YW,NULL AS YY,ROW_NUMBER()OVER(PARTITION BY MON ORDER BY SX DESC)NB1
  FROM TEST)T1
WHERE T1.NB1<3 
    )DD_1 
     LEFT JOIN 
    (
SELECT  T2.MON,T2.NAME,T2.YW,NB2
FROM (SELECT MON,NAME,YW,ROW_NUMBER()OVER(PARTITION BY MON ORDER BY YW DESC)NB2
  FROM TEST)T2
WHERE T2.NB2<3 )DD_2 
    ON DD_1.MON=DD_2.MON
        AND DD_1.NB1=DD_2.NB2
    LEFT JOIN 
   (
SELECT  T3.MON,T3.NAME,T3.YY,NB3
FROM (SELECT MON,NAME,YY,ROW_NUMBER()OVER(PARTITION BY MON ORDER BY YY DESC)NB3
  FROM TEST)T3
WHERE T3.NB3<3)DD_3 
    ON DD_1.MON=DD_3.MON
       AND DD_1.NB1=DD_3.NB3

Answer 3

create table t14(月份 varchar(4), 姓名 varchar(4), 数学 int, 语文 int, 英语 int)insert into t14
select '1月','小明','86','92','70' union all
select '1月','小王','72','58','90' union all
select '1月','小张','86','88','72' union all
select '2月','小明','85','90','76' union all
select '2月','小王','75','70','88' union all
select '2月','小张','93','87','73' union all
select '3月','小明','75','91','90' union all
select '3月','小王','77','63','89'select 月份,
'小明' '姓名',
isnull(max(case when 姓名='小明' then 数学 end),0) '数学最高成绩',
'小王' '姓名',
isnull(max(case when 姓名='小王' then 语文 end),0) '语文最高成绩',
'小张' '姓名',
isnull(max(case when 姓名='小张' then 英语 end),0) '英语最高成绩'
from t14
group by 月份月份   姓名   数学最高成绩      姓名   语文最高成绩      姓名   英语最高成绩
---- ---- ----------- ---- ----------- ---- -----------
1月   小明   86          小王   58          小张   72
2月   小明   85          小王   70          小张   73
3月   小明   75          小王   63          小张   0

Answer 4

create table #tb(月份 varchar(20),姓名 varchar(20),数学 int,语文 int,英语 int)
insert into #tb
select '1月','小明',86,92,70
union all
select '1月','小王',72,58,90
union all
select '1月','小张',86,88,72
union all
select '2月','小明',85,90,76
union all
select '2月','小王',75,70,88
union all
select '2月','小张',93,87,73
union all
select '3月','小明',75,91,90
union all
select '3月','小王',77,63,89select a.月份,a.姓名,a.数学,b.姓名,b.语文,c.姓名,c.英语 from (select 月份,姓名,max(数学) as 数学 from  #tb group by 月份,姓名) a
left join (select 月份,姓名,max(语文) as 语文 from  #tb group by 月份,姓名) b on a.月份=b.月份 
left join (select 月份,姓名,max(英语) as 英语 from  #tb group by 月份,姓名) c on a.月份=c.月份 
where 数学 in (select max(数学) from #tb group by 月份) and 语文 in (select max(语文) from #tb group by 月份) and 英语 in(select max(英语) from #tb group by 月份)