select 班级,姓名,科目,max(成绩) from 表 where  成绩 in (select max(成绩) from 表) and 成绩>80

解决方案 »

  1.   

    top 2啊,注意,不是一个简单的最大值问题。
      

  2.   

    一楼的方法不是根本的解决方案!
    如果还有很多的学生最高分没有80分呢?考虑ing...
      

  3.   

    declare @t table(班级 varchar(2),  姓名 varchar(2),  科目 varchar(2),  成绩 int)insert @t
    select '1','a','甲','98'
    union
    select '1','a','乙','90'
    union
    select '1','b','甲','65'
    union
    select '1','b','乙','91'
    union
    select '1','b','丙','88'
    union
    select '1','b','丁','70'
    union
    select '2','c','甲','94'
    union
    select '2','c','丙','75'
    union
    select '2','c','乙','90'
    union
    select '2','c','丁','85'
    union
    select '2','c','甲','58'
    union
    select '2','d','乙','99'
    union
    select '2','d','丙','85'
    union
    select '2','d','丁','75'
    select 班级,姓名,科目,max(成绩) from @t where 成绩>80
    group by 班级,姓名,科目
      

  4.   

    azsoft的方法明显也不行。
    最起码的,要有top 2、要有group by。
    我确实不知道怎么做了。
    大家当作一个趣味题来考虑罢。
      

  5.   

    -- TESTCREATE TABLE #tmp(班级 int,姓名 varchar(10),科目 varchar(10), 成绩 tinyint)
    INSERT #tmp
    SELECT   1,     'a',     '甲',    98
    UNION ALL SELECT 1,     'a',     '乙',    90
    UNION ALL SELECT 1,     'a',     '丙',    56
    UNION ALL SELECT 1,     'a',     '丁',    70
    UNION ALL SELECT 1,     'b',     '甲',    65
    UNION ALL SELECT 1,     'b',     '乙',    91
    UNION ALL SELECT 1,     'b',     '丙',    88
    UNION ALL SELECT 1,     'b',     '丁',    70
    UNION ALL SELECT 2,     'c',     '甲',    94
    UNION ALL SELECT 2,     'c',     '乙',    45
    UNION ALL SELECT 2,     'c',     '丙',    75
    UNION ALL SELECT 2,     'c',     '丁',    85
    UNION ALL SELECT 2,     'd',     '甲',    58
    UNION ALL SELECT 2,     'd',     '乙',    99
    UNION ALL SELECT 2,     'd',     '丙',    85
    UNION ALL SELECT 2,     'd',     '丁',    75SELECT * FROM #tmpSELECT * 
    FROM #tmp a
    WHERE 
    (SELECT COUNT(*) FROM #tmp WHERE 班级 = a.班级 AND 姓名 = a.姓名 AND 成绩 >a.成绩) IN (0,1)DROP TABLE #tmp/*
    班级          姓名         科目         成绩   
    ----------- ---------- ---------- ---- 
    1           a          甲          98
    1           a          乙          90
    1           a          丙          56
    1           a          丁          70
    1           b          甲          65
    1           b          乙          91
    1           b          丙          88
    1           b          丁          70
    2           c          甲          94
    2           c          乙          45
    2           c          丙          75
    2           c          丁          85
    2           d          甲          58
    2           d          乙          99
    2           d          丙          85
    2           d          丁          75

    (所影响的行数为 16 行)

    班级          姓名         科目         成绩   
    ----------- ---------- ---------- ---- 
    1           a          甲          98
    1           a          乙          90
    1           b          乙          91
    1           b          丙          88
    2           c          甲          94
    2           c          丁          85
    2           d          乙          99
    2           d          丙          85

    (所影响的行数为 8 行)
    */
      

  6.   

    成绩>80不能这么考虑,如果再加几个学生的成绩呢: azsoft(Try my best)  可以试试:班级  姓名 科目  成绩
    ...
    3     h     甲    85
    3     h     乙    79
    3     h     丙    56
    3     h     丁    70
    3     j     甲    78
    3     j     乙    83
    3     j     丙    75
    3     j     丁    70
    ...
      

  7.   

    烛光:
      诚心向您请教,
    WHERE SELECT COUNT(*) FROM #tmp WHERE 班级 = a.班级 AND 姓名 = a.姓名 AND 成绩 >a.成绩) IN (0,1)是什么意思啊?--您介绍一下您的思想好吗?
      

  8.   

    select 班级  姓名 科目  成绩 from 表
    where 成绩 in
    (select max(成绩) from 表)
    union
    select 班级  姓名 科目  成绩 from 表
    where 成绩 in
    (select max(成绩) from 表
    where 成绩 not in (select max(成绩) from 表)
    )不好意思,太啰唆了
      

  9.   

    烛光:
        我明白了:
        (SELECT COUNT(*) FROM #tmp WHERE 班级 = a.班级 AND 姓名 = a.姓名 AND 成绩 >a.成绩)=0 代表的是成绩是每一个学生的最高分。可是我不明白文什么会这样。
        :(
        我不用sql很多年,成白痴了。
        :(..
      

  10.   

    select *
    from T a
    where 成绩 in (select Top 2 成绩 
                     from T 
                    where 姓名 = a.姓名
                 order by 成绩 desc
                    )
    order by 班级,姓名,成绩 desc
      

  11.   

    select a.* from #tmp a where 科目 in
    (select top 2 科目 from #tmp b where b.姓名=a.姓名 order by b.成绩 desc) order by a.姓名,a.成绩 desc
      

  12.   

    建议你去看看这篇文章:
    一道褒贬不一的 SQL 考试题 
    http://www.csdn.net/Develop/Read_Article.asp?Id=1598910.列印各科成绩前三名的记录:(不考虑成绩并列情况)
      学生ID,学生姓名,课程ID,课程名称,成绩,教师ID,教师姓名  如果仅从成绩考虑前三名的人,利用相关子查询的知识: SELECT * 
       FROM 成绩表 t1
      WHERE 成绩 IN (SELECT TOP 3 成绩
                   FROM 成绩表
                   WHERE t1.课程id = 课程id
                ORDER BY 成绩 DESC
                  )
    ORDER BY t1.课程id  这样查询的结果各科成绩前三名的记录数应该大于等于三,因为可能有并列情况,
      如果小于三自然是该门课还没有那么多人考试!
      如果不考虑并列情况,严格控制各科只列印三条记录,则使用"学生id"构造相关
      子查询条件亦可:  SELECT * 
        FROM 成绩表 t1
       WHERE 学生id IN (SELECT TOP 2 学生id
                          FROM 成绩表
                         WHERE t1.课程id = 课程id
                      ORDER BY 成绩 DESC
                        )
    ORDER BY t1.课程id
      

  13.   

    select a.* from #tmp a where 科目 in
    (select top 2 科目 from #tmp b where b.姓名=a.姓名 order by b.成绩 desc) order by a.姓名,a.成绩 desc
    ---这个楼主没试吗?应该可以的!
      

  14.   

    --测试:
    CREATE TABLE #tmp(班级 int,姓名 varchar(10),科目 varchar(10), 成绩 tinyint)INSERT #tmp
    SELECT   1,     'a',     '甲',    98
    UNION ALL SELECT 1,     'a',     '乙',    90
    UNION ALL SELECT 1,     'a',     '丙',    56
    UNION ALL SELECT 1,     'a',     '丁',    90
    UNION ALL SELECT 1,     'b',     '甲',    65
    UNION ALL SELECT 1,     'b',     '乙',    91
    UNION ALL SELECT 1,     'b',     '丙',    88
    UNION ALL SELECT 1,     'b',     '丁',    70
    UNION ALL SELECT 2,     'c',     '甲',    94
    UNION ALL SELECT 2,     'c',     '乙',    45
    UNION ALL SELECT 2,     'c',     '丙',    75
    UNION ALL SELECT 2,     'c',     '丁',    85
    UNION ALL SELECT 2,     'd',     '甲',    58
    UNION ALL SELECT 2,     'd',     '乙',    99
    UNION ALL SELECT 2,     'd',     '丙',    99
    UNION ALL SELECT 2,     'd',     '丁',    75
    Go
    ---select a.* from #tmp a where 科目 in
    (select top 2 科目 from #tmp b where b.姓名=a.姓名 order by b.成绩 desc) order by a.姓名,a.成绩 desc
    ---结果:
    班级          姓名         科目         成绩   
    ----------- ---------- ---------- ---- 
    1           a          甲          98
    1           a          乙          90
    1           b          乙          91
    1           b          丙          88
    2           c          甲          94
    2           c          丁          85
    2           d          乙          99
    2           d          丙          99(8 row(s) affected)
      

  15.   

    declare @t0 table(班级 varchar(2),  姓名 varchar(2),  科目 varchar(2),  成绩 int)
    declare @t1 table(班级 varchar(2),  姓名 varchar(2),  科目 varchar(2),  成绩 int)
    declare @t2 table(id int identity(1,1), 班级 varchar(2),  姓名 varchar(2))
    declare @i int, @row_count int
    declare @班级 varchar(2),  @姓名 varchar(2)insert @t1
    select '1','a','甲','98'
    union
    select '1','a','乙','90'
    union
    select '1','b','甲','65'
    union
    select '1','b','乙','91'
    union
    select '1','b','丙','88'
    union
    select '1','b','丁','70'
    union
    select '2','c','甲','94'
    union
    select '2','c','丙','75'
    union
    select '2','c','乙','90'
    union
    select '2','c','丁','85'
    union
    select '2','c','甲','58'
    union
    select '2','d','乙','99'
    union
    select '2','d','丙','85'
    union
    select '2','d','丁','75'
    insert into @t2
    select distinct 班级, 姓名 from @t1set  @i=1
    select @row_count=count(*) from @t2
    while @i<=@row_count
    begin
    select @班级=班级, @姓名=姓名 from @t2 where id=@i
    insert into @t0
    select top 2 * from @t1 where @班级=班级 and @姓名=姓名 order by 班级, 姓名, 成绩
    set @i=@i+1
    endselect * from @t0
      

  16.   

    wzh1215(懒猫) 的如果在不同班级同一个人同一个科目有相同记录的时候就不正确了,如下:SELECT   1,     'a',     '甲',    99
    UNION ALL SELECT 1,     'a',     '乙',    99
    UNION ALL SELECT 1,     'a',     '丙',    99
    UNION ALL SELECT 1,     'a',     '丁',    98
    UNION ALL SELECT 1,     'b',     '甲',    65
    UNION ALL SELECT 1,     'b',     '乙',    91
    UNION ALL SELECT 1,     'b',     '丙',    88
    UNION ALL SELECT 1,     'b',     '丁',    70
    UNION ALL SELECT 2,     'a',     '甲',    99
    UNION ALL SELECT 2,     'a',     '乙',    99
    UNION ALL SELECT 2,     'a',     '丙',    98
    UNION ALL SELECT 2,     'a',     '丁',    85
    UNION ALL SELECT 2,     'd',     '甲',    58
    UNION ALL SELECT 2,     'd',     '乙',    99
    UNION ALL SELECT 2,     'd',     '丙',    85
    UNION ALL SELECT 2,     'd',     '丁',    75
    UNION ALL SELECT 3,     'h',     '甲',    85
    UNION ALL SELECT 3,     'h',     '乙',    79
    UNION ALL SELECT 3,     'h',     '丙',    56
    UNION ALL SELECT 3,     'h',     '丁',    70
    UNION ALL SELECT 3,     'j',     '甲',    78
    UNION ALL SELECT 3,     'j',     '乙',    83
    UNION ALL SELECT 3,     'j',     '丙',    75
    UNION ALL SELECT 3,     'j',     '丁',    70
      

  17.   

    这个问题的具体应用是一个订单跟踪系统,表结构如下:
    CREATE TABLE [dbo].[TEMnum] (
    [例外种类] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
    [原因] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [部门] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
    [年] [int] NULL ,
    [季度] [varchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
    [月] [int] NULL ,
    [周次] [int] NULL ,
    [数量] [int] NULL 

    例外种类:延迟、退库、取消..
    原因:每种例外有若干种原因,如:车抛锚、...
    数量:不同例外种类的的不同原因的发生数量
    用户的要求是:要列出每个具体时间段(每周)的每个部门的每种例外种类的出现数目最多的两种原因,如不够两种可以空缺;如超过两种只选两种;如数量有重复随便选一种原因.
    干部老兄,我的问题如果加到“一道褒贬不一的 SQL 考试题”里面去,应该是“列印每个学生成绩最高的两门课程....”还有区别:每个学生选修的课程数目可能不是一样的,可能<2。
      

  18.   

    --那就这样好了!
    CREATE TABLE #tmp(班级 int,姓名 varchar(10),科目 varchar(10), 成绩 tinyint)INSERT #tmp
    SELECT   1,     'a',     '甲',    99
    UNION ALL SELECT 1,     'a',     '乙',    99
    UNION ALL SELECT 1,     'a',     '丙',    99
    UNION ALL SELECT 1,     'a',     '丁',    98
    UNION ALL SELECT 1,     'b',     '甲',    65
    UNION ALL SELECT 1,     'b',     '乙',    91
    UNION ALL SELECT 1,     'b',     '丙',    88
    UNION ALL SELECT 1,     'b',     '丁',    70
    UNION ALL SELECT 2,     'a',     '甲',    99
    UNION ALL SELECT 2,     'a',     '乙',    99
    UNION ALL SELECT 2,     'a',     '丙',    98
    UNION ALL SELECT 2,     'a',     '丁',    85
    UNION ALL SELECT 2,     'd',     '甲',    58
    UNION ALL SELECT 2,     'd',     '乙',    99
    UNION ALL SELECT 2,     'd',     '丙',    85
    UNION ALL SELECT 2,     'd',     '丁',    75
    UNION ALL SELECT 3,     'h',     '甲',    85
    UNION ALL SELECT 3,     'h',     '乙',    79
    UNION ALL SELECT 3,     'h',     '丙',    56
    UNION ALL SELECT 3,     'h',     '丁',    70
    UNION ALL SELECT 3,     'j',     '甲',    78
    UNION ALL SELECT 3,     'j',     '乙',    83
    UNION ALL SELECT 3,     'j',     '丙',    75
    UNION ALL SELECT 3,     'j',     '丁',    70
    Go
    select a.* from #tmp a where a.科目+','+cast(班级 as varchar(10)) in
    (select top 2 科目+','+cast(班级 as varchar(10)) from #tmp b where a.班级=b.班级 and b.姓名=a.姓名 order by b.成绩 desc) order by a.班级,a.姓名,a.成绩 desc
    drop table #tmp
    ---------
    --结果:班级          姓名         科目         成绩   
    ----------- ---------- ---------- ---- 
    1           a          甲          99
    1           a          乙          99
    1           b          乙          91
    1           b          丙          88
    2           a          甲          99
    2           a          乙          99
    2           d          乙          99
    2           d          丙          85
    3           h          甲          85
    3           h          乙          79
    3           j          乙          83
    3           j          甲          78(12 row(s) affected)----别再说一个班级再有同名的哟,那就得改表结构了!
      

  19.   

    select a.* from #tmp a where 科目 in
    (select top 2 科目 from #tmp b where b.姓名=a.姓名 order by b.成绩 desc) order by a.姓名,a.成绩 desc/// 同意懒猫
      

  20.   

    /// 同意懒猫
    哈哈,你的可以了,厉害~``我傻傻的搞到现在还是没有搞出来。还是要通过top 2 来做。
      

  21.   

    select * from #T1 a
    where 成绩 in (select Top 2 成绩 
                     from #T1
                    where 班级=a.班级 and 姓名 = a.姓名
                    order by 成绩 desc
                    )
    order by 班级,姓名,成绩 desc
      

  22.   

    众位师兄,不能简单的top 2 啊,要找的是根据分组之后班级、姓名分组之后的top 2啊~~~!
      

  23.   

    如果表里面有个字段id(唯一),那就简单多了,可以用:
    SELECT * 
        FROM 成绩表 t1
       WHERE 学生id IN (SELECT TOP 2 学生id
                          FROM 成绩表
                         WHERE t1.课程id = 课程id
                      ORDER BY 成绩 DESC
                        )
    ORDER BY t1.课程id
    ,如果没有,就
    select identity(int,1,1) id,* into #tmp
    然后通过上面的方法应该是可以的。
      

  24.   

    -- TESTCREATE TABLE #tmp(班级 int,姓名 varchar(10),科目 varchar(10), 成绩 tinyint)
    INSERT #tmp
    SELECT   1,     'a',     '甲',    99
    UNION ALL SELECT 1,     'a',     '乙',    98
    UNION ALL SELECT 1,     'a',     '丙',    98
    UNION ALL SELECT 1,     'a',     '丁',    98
    UNION ALL SELECT 1,     'b',     '甲',    65
    UNION ALL SELECT 1,     'b',     '乙',    91
    UNION ALL SELECT 1,     'b',     '丙',    88
    UNION ALL SELECT 1,     'b',     '丁',    70
    UNION ALL SELECT 2,     'a',     '甲',    99
    UNION ALL SELECT 2,     'a',     '乙',    99
    UNION ALL SELECT 2,     'a',     '丙',    98
    UNION ALL SELECT 2,     'a',     '丁',    85
    UNION ALL SELECT 2,     'd',     '甲',    58
    UNION ALL SELECT 2,     'd',     '乙',    99
    UNION ALL SELECT 2,     'd',     '丙',    85
    UNION ALL SELECT 2,     'd',     '丁',    75
    UNION ALL SELECT 3,     'h',     '甲',    85
    UNION ALL SELECT 3,     'h',     '乙',    79
    UNION ALL SELECT 3,     'h',     '丙',    56
    UNION ALL SELECT 3,     'h',     '丁',    70
    UNION ALL SELECT 3,     'j',     '甲',    78
    UNION ALL SELECT 3,     'j',     '乙',    83
    UNION ALL SELECT 3,     'j',     '丙',    75
    UNION ALL SELECT 3,     'j',     '丁',    70--SELECT * FROM #tmpselect identity(int,1,1) 学生id,* into #成绩表 from #tmpSELECT * 
        FROM #成绩表 t1
       WHERE 学生id IN (SELECT TOP 2 学生id
                          FROM #成绩表
                         WHERE t1.班级 = 班级 AND t1.姓名 = 姓名
                      ORDER BY 成绩 DESC
                        )drop table #tmp,#成绩表
      

  25.   

    有4个99还好办,select * from #T1 a
    where 科目  in (select Top 2 科目 
                     from #T1
                    where 班级=a.班级 and 姓名 = a.姓名
                    order by 成绩 desc
                    )
    order by 班级,姓名,成绩 desc
    对应你的实际问题,可以这样写select * from #t1 a
    where 原因 in (select top 2 原因 from #t1
                where 例外大类=a.例外大类 and 部门=a.部门
                order by 数量 desc)
          and 具体时间....
    order by 部门,例外大类,原因,数量 desc最后的order by是显示的结果排列
      

  26.   

    gmlxf(烛光) 最后使用的方法应该是正确的。再会!各位。