大侠进来瞧瞧

Question

下班前在看LKmm发的那个学生成绩的例子，有个例子蛮复杂的
--查询如下课程成绩第 2 名到第 4 名的学生成绩单：数学，物理，化学，政治
 --查询结果这样显示：   [学生ID] [学生姓名] 数学 物理 化学 政治 总分现有的代码这样的，菜鸟的我感觉这个写的好复杂SELECT  DISTINCT top 3 
      SC.S# As 学生学号, 
        Student.Sname AS 学生姓名 , 
      T1.score AS 数学, 
      T2.score AS 物理, 
      T3.score AS 化学, 
      T4.score AS 政治, 
      ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 
      FROM Student,SC  LEFT JOIN SC AS T1 
                      ON SC.S# = T1.S# AND T1.C# = '001' 
            LEFT JOIN SC AS T2 
                      ON SC.S# = T2.S# AND T2.C# = '002' 
            LEFT JOIN SC AS T3 
                      ON SC.S# = T3.S# AND T3.C# = '003' 
            LEFT JOIN SC AS T4 
                      ON SC.S# = T4.S# AND T4.C# = '004' 
      WHERE student.S#=SC.S# and 
      ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 
      NOT IN 
      (SELECT 
            DISTINCT 
            TOP 15 WITH TIES 
            ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 
      FROM sc 
            LEFT JOIN sc AS T1 
                      ON sc.S# = T1.S# AND T1.C# = 'k1' 
            LEFT JOIN sc AS T2 
                      ON sc.S# = T2.S# AND T2.C# = 'k2' 
            LEFT JOIN sc AS T3 
                      ON sc.S# = T3.S# AND T3.C# = 'k3' 
            LEFT JOIN sc AS T4 
                      ON sc.S# = T4.S# AND T4.C# = 'k4' 
      ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC)
问问大家能不能有简单点的呢？

Answer 1

我把表结果建好
--Student(S#,Sname,Sage,Ssex) 学生表 
--Course(C#,Cname,T#) 课程表 
--SC(S#,C#,score) 成绩表 
--Teacher(T#,Tname) 教师表 
IF OBJECT_ID('STU')IS NOT NULL
DROP TABLE STU
GO
IF OBJECT_ID('COURSE')IS NOT NULL
DROP TABLE COURSE
GO
IF OBJECT_ID('SC')IS NOT NULL
DROP TABLE SC
GO
IF OBJECT_ID('TEA')IS NOT NULL
DROP TABLE TEA
GO
create table stu (S# varchar(8) ,sname varchar(8))
insert into stu
select 's001', '王'union all
select 's002', '李'union all
select 's003', '齐'union all
select 's004', '田'union all
select 's005', '陈'union all
select 's006', '天'
gocreate table course (C# varchar(8),cname varchar(10),T# varchar(8))
insert into course
select 'C001', '数学','T001'union all
select 'C002', '物理','T002'union all
select 'C003', '化学','T003'union all
select 'C004', '政治','T004'union all
select 'C005', '历史','T005'union all
select 'C006', '语文','T006'
gocreate table sc (s# varchar(8) ,c# varchar(8),score int)
insert into sc
select 'S001','C001',88 UNION ALL
select 'S001','C002',89 UNION ALL
select 'S001','C003',80 UNION ALL
select 'S001','C004',84 UNION ALL
select 'S001','C005',60 UNION ALL
select 'S001','C006',39 UNION ALL
select 'S002','C001',77 UNION ALL
select 'S002','C002',66 UNION ALL
select 'S002','C003',100 UNION ALL
select 'S002','C004',30 UNION ALL
select 'S002','C005',56 UNION ALL
select 'S002','C006',85 UNION ALL
select 'S003','C001',83 UNION ALL
select 'S003','C002',82 UNION ALL
select 'S003','C003',85 UNION ALL
select 'S003','C004',88 UNION ALL
select 'S003','C005',89 UNION ALL
select 'S003','C006',80 UNION ALL
select 'S004','C001',84 UNION ALL
select 'S004','C002',60 UNION ALL
select 'S004','C003',39 UNION ALL
select 'S004','C004',77 UNION ALL
select 'S004','C005',66 UNION ALL
select 'S004','C006',100 UNION ALL
select 'S005','C001',30 UNION ALL
select 'S005','C002',56 UNION ALL
select 'S005','C003',85 UNION ALL
select 'S005','C004',83 UNION ALL
select 'S005','C005',82 UNION ALL
select 'S005','C006',85 UNION ALL
select 'S006','C001',77 UNION ALL
select 'S006','C002',66 UNION ALL
select 'S006','C003',100 UNION ALL
select 'S006','C004',30 UNION ALL
select 'S006','C005',56 UNION ALL
select 'S006','C006',85 
GOCREATE TABLE TEA(T# VARchar(8),tname varchar(8))
insert into tea 
select 'T001','王武'union all
select 'T002','麻子'union all
select 'T003','小单'union all
select 'T004','邱全'union all
select 'T005','范岗'union all
select 'T006','哈哈'union all
select 'T007','小样'
go

Answer 2

select
    a.[S#],
    a.sname,
    数学=sum(case b.cname when '数学' then c.score else 0 end),
    物理=sum(case b.cname when '物理' then c.score else 0 end),
    化学=sum(case b.cname when '化学' then c.score else 0 end),
    政治=sum(case b.cname when '政治' then c.score else 0 end),
    总分=sum(c.score)
from
    stu a,course b,sc c
where
    a.[S#]=c.[S#] and b.[C#]=c.[C#]
    and
    c.[s#] in (select 
                 top 3 WITH TIES [s#] 
             from 
                 (select top 4 WITH TIES [s#],sum(score) as score from sc group by [s#] order by score desc) m
             order by 
                 m.score asc)
group by
    a.[S#],a.sname
order by
    总分 desc/*
S#       sname    数学          物理          化学          政治          总分          
-------- -------- ----------- ----------- ----------- ----------- ----------- 
s001     王        88          89          80          84          440
s004     田        84          60          39          77          426
s005     陈        30          56          85          83          421
*/

Answer 3

如果只计算四门成绩，以上SQL还需要修改：select
    a.[S#],
    a.sname,
    数学=sum(case b.cname when '数学' then c.score else 0 end),
    物理=sum(case b.cname when '物理' then c.score else 0 end),
    化学=sum(case b.cname when '化学' then c.score else 0 end),
    政治=sum(case b.cname when '政治' then c.score else 0 end),
    总分=sum(c.score)
from
    stu a,course b,sc c
where
    a.[S#]=c.[S#] and b.[C#]=c.[C#]
    and
    b.cname in('数学','物理','化学','政治')
    and
    c.[s#] in (select 
                 top 3 WITH TIES [s#] 
             from 
                 (select 
                      top 4 WITH TIES [s#],sum(score) as score 
                  from 
                      sc t 
                  where
                      t.[c#] in(select [c#] from course where cname in('数学','物理','化学','政治'))  
                  group by 
                      [s#] 
                  order by 
                      score desc) m
             order by 
                 m.score asc)
group by
    a.[S#],a.sname
order by
    总分 desc/*
S#       sname    数学          物理          化学          政治          总分          
-------- -------- ----------- ----------- ----------- ----------- ----------- 
s003     齐        83          82          85          88          338
s002     李        77          66          100         30          273
s006     天        77          66          100         30          273
*/

Answer 4

我去掉WITH TIES 结果也是一样，还很少用这个。

Answer 5

主要是数据不巧合create table sc (s# varchar(8) ,c# varchar(8),score int)
insert into sc
select 'S001','C001',88 UNION ALL
select 'S001','C002',89 UNION ALL
select 'S001','C003',80 UNION ALL
select 'S001','C004',84 UNION ALL
select 'S001','C005',80 UNION ALL
select 'S001','C006',39 
GOselect top 4 [s#], (score) as score from sc   order by score desc
s#       score       
-------- ----------- 
S001     89
S001     88
S001     84
S001     80（所影响的行数为 4 行）select top 4 with ties [s#], (score) as score from sc   order by score descs#       score       
-------- ----------- 
S001     89
S001     88
S001     84
S001     80
S001     80（所影响的行数为 5 行）
drop table sc

Answer 6

thank you for happyflystone!