遇到一相当难的查询题如相，如何求得第3到6名的记录

jiang5495 2009-12-11 08:58:53
有如据库表如下:
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表


查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（c#=001），马克思（c#=002），UML （c#=003），数据库c#=004）
查询出的结果列如下：
[学生ID] [学生姓名] 企业管理 马克思 UML 数据库 平均成绩



...全文
600 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
fcuandy 2009-12-11
  • 打赏
  • 举报
 回复
手误把连接写漏了，补一下。

SELECT a.s#,sname,c001,c002,c003,avg_score
FROM student a
INNER JOIN
(
SELECT TOP 4 s#,
MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c001,
MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c002,
MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c003,
MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c004,
AVG(score) avg_score
FROM sc
WHERE c# IN ('001','002','003','004')
GROUP BY s#
HAVING
ss<
(SELECT MIN(x) mx
FROM
(
SELECT TOP 2 SUM(score) ss
FROM sc
WHERE c# IN ('001','002','003','004')
GROUP BY s#
ORDER BY ss
) a
)
ORDER BY ss DESC
) b
ON a.s#=b.s#
ORDER BY avg_score
fcuandy 2009-12-11
  • 打赏
  • 举报
 回复
上面将top 3改为top 4

若楼主意思是每门3-6,那应该有12条记录。
fcuandy 2009-12-11
  • 打赏
  • 举报
 回复
效率可能好一点点。

SELECT a.s#,sname,c001,c002,c003,avg_score

	FROM

	(

	SELECT TOP 3 s#,

		MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c001,

		MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c002,

		MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c003,

		MAX(CASE WHEN c#='001' THEN score ELSE 0 END) c004,

		AVG(score) avg_score

		FROM sc 

		WHERE c# IN ('001','002','003','004')

		GROUP BY s#

		HAVING 

			ss<

			(SELECT MIN(x) mx

				FROM

				(

					SELECT TOP 2 SUM(score) ss 

						FROM sc 

						WHERE c# IN ('001','002','003','004')

						GROUP BY s#

						ORDER BY ss

				) a

			)

		ORDER BY ss DESC

	) a

	ORDER BY avg_score
ACMAIN_CHM 2009-12-11
  • 打赏
  • 举报
 回复
[Quote]这么有六项结果，
不是查第三到第六名吗。
不过很很强大，
学习了，
我认真看一下这段代码，学习下[/Quote]


[Quote]查询如下课程成绩第 3 名到第 6 名的学生成绩单[/Quote]
每门第三名到第六名。
jiang5495 2009-12-11
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 fcuandy 的回复:]
难度级数0星, 只是烦琐而已
[/Quote]
不好意思，
因为本人对SQL的相关查询还不是很强，
所以觉得比较难！
决不是有意夸大此题难度。
jiang5495 2009-12-11
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 acmain_chm 的回复:]
引用这段代码的执行结果报错了，
服务器: 消息 209，级别 16，状态 1，行 13
列名 's#' 不明确。

改成如下

SQL codeselect Student.S#,Sname,max(case C#when001then scoreend)as 企业管理,max(case C#when002then scoreend)as 马克思,max(case C#when003then scoreend)as UML,max(case C#when004then scoreend)as 数据库,avg(score)as 平均成绩from Studentinnerjoin SCon Student.S#=SC.S#and Student.S#in (select S#from SC t1where (selectcount(*)from SCwhere C#=t1.C#and score>=t1.score)between3and6and C#in (001,002,003,004)
)groupby Student.S#,Sname
[/Quote]

这段代码的运行结果是：
1008 郭勤 85 75 NULL NULL 80
1001 胡尔 87 62 NULL 84 77
1004 黄题 67 NULL NULL 87 77
1005 黄英 NULL 69 89 NULL 79
1002 瞿婷 64 NULL 78 NULL 71
1006 张清 56 85 NULL 96 79
这么有六项结果，
不是查第三到第六名吗。
不过很很强大，
学习了，
我认真看一下这段代码，学习下
fcuandy 2009-12-11
  • 打赏
  • 举报
 回复
难度级数0星, 只是烦琐而已
ACMAIN_CHM 2009-12-11
  • 打赏
  • 举报
 回复
[Quote]这段代码的执行结果报错了，
服务器: 消息 209，级别 16，状态 1，行 13
列名 's#' 不明确。[/Quote]

改成如下

select Student.S#,Sname,

    max(case C# when 001 then score end) as 企业管理,

    max(case C# when 002 then score end) as 马克思,

    max(case C# when 003 then score end) as UML,

    max(case C# when 004 then score end) as 数据库,

    avg(score) as 平均成绩

from Student inner join SC on Student.S#=SC.S#

and Student.S# in (

    select S#

    from SC t1

    where (select count(*) from SC where C#=t1.C# and score>=t1.score) between 3 and 6

    and C# in (001,002,003,004)

)

group by Student.S#,Sname
jiang5495 2009-12-11
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 acmain_chm 的回复:]
SQL codeselect Student.S#,Sname,max(case C#when001then scoreend)as 企业管理,max(case C#when002then scoreend)as 马克思,max(case C#when003then scoreend)as UML,max(case C#when004then scoreend)as 数据库,avg(score)a?-
[/Quote]
这段代码的执行结果报错了，
服务器: 消息 209，级别 16，状态 1，行 13
列名 's#' 不明确。
jiang5495 2009-12-11
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 fredrickhu 的回复:]
http://blog.csdn.net/fredrickhu/archive/2009/09/25/4592668.aspx
[/Quote]
这段代码的执行结果报错了，
服务器: 消息 209，级别 16，状态 1，行 13
列名 's#' 不明确。
bancxc 2009-12-11
  • 打赏
  • 举报
 回复
都好强啊
ACMAIN_CHM 2009-12-11
  • 打赏
  • 举报
 回复 
select Student.S#,Sname,

	max(case C# when 001 then score end) as 企业管理,

	max(case C# when 002 then score end) as 马克思,

	max(case C# when 003 then score end) as UML,

	max(case C# when 004 then score end) as 数据库,

	avg(score) as 平均成绩

from Student inner join SC on Student.S#=SC.S#

and S# in (

	select S#

	from SC t1

	where (select count(*) from SC where C#=t1.C# and score>=t1.score) between 3 and 6

	and C# in (001,002,003,004)

)

group by Student.S#,Sname
--小F-- 2009-12-11
  • 打赏
  • 举报
 回复 
SELECT  DISTINCT top 3  



      SC.S# As 学生学号,  



        Student.Sname AS 学生姓名,  



      T1.score AS 企业管理,  



      T2.score AS 马克思,  



      T3.score AS UML,  



      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);  





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/25/4592668.aspx
jiang5495 2009-12-11
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 dtxh168 的回复:]
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
SQL codeSELECTDISTINCTtop3
SC.S#As 学生学号,
Student.SnameAS 学生姓名 ,
T1.scoreAS 企业管理,
T2.scoreAS 马克思,
T3.scoreAS UML,
T4.scoreAS 数据库,ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)as 总分FROM Student,SCLEFTJOIN SCAS T1ON SC.S#= T1.S#AND T1.C#='001'LEFTJOIN SCAS T2ON SC.S#= T2.S#AND T2.C#='002'LEFTJOIN SCAS T3ON SC.S#= T3.S#AND T3.C#='003'LEFTJOIN SCAS T4ON SC.S#= T4.S#AND T4.C#='004'WHERE student.S#=SC.S#andISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)NOTIN
(SELECTDISTINCTTOP15WITH TIESISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)FROM scLEFTJOIN scAS T1ON sc.S#= T1.S#AND T1.C#='k1'LEFTJOIN scAS T2ON sc.S#= T2.S#AND T2.C#='k2'LEFTJOIN scAS T3ON sc.S#= T3.S#AND T3.C#='k3'LEFTJOIN scAS T4ON sc.S#= T4.S#AND T4.C#='k4'ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC);
[/Quote]

答案和题目要求不符啊，
它求的是总分而不是平均分，
再者求出来的结果也不对，
不信你可以试一下。
让你望见影子的墙 2009-12-11
  • 打赏
  • 举报
 回复 
declare @t1 table(id int ,name char(10))

declare @t2 table(id int ,result int)



insert into @t1 

select 1,'wang' union 

select 2,'hero' union 

select 3,'wyq'



insert into @t2

select 1,40 union all

select 1,90 union all

select 1,50 union all

select 1,78 union all

select 2,89 union all

select 2,34 union all

select 2,67 union all

select 2,75 



--获取每个id的最大的两个值（相当于内连接）

select *

from @t1 s cross apply (select top 2 * from @t2 t where id=s.id order by result desc) t

--获取每个id的最大的两个值（相当于外连接）

select *

from @t1 s outer apply (select top 2 * from @t2 t where id=s.id order by result desc) t

--获取每个id的第二个和第三个值

select * 

from @t1 s cross apply (select * from (select row=row_number() over (partition by id order 



by result desc),* from @t2)K where id=s.id and row between 2 and 4)K


然后行列转换，自己到精华帖中找吧
weixiaoshashou 2009-12-11
  • 打赏
  • 举报
 回复
1楼强，两分钟就写出来了
puhaiggp 2009-12-11
  • 打赏
  • 举报
 回复
不错，值得学习
dtxh168 2009-12-11
  • 打赏
  • 举报
 回复
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 
    SELECT  DISTINCT top 3 

      SC.S# As 学生学号, 

        Student.Sname AS 学生姓名 , 

      T1.score AS 企业管理, 

      T2.score AS 马克思, 

      T3.score AS UML, 

      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);
Mr_Nice 2009-12-11
  • 打赏
  • 举报
 回复
学习了
加载更多回复（1）
CF 绿蓝题做题记录
CF 绿蓝题做题记录 题单 最近有在经常随机一些 CF 绿蓝难度的题目，用于提升思维。这里专门开个坑，记录一下思路障碍经验什么的。 自然这些内容也在每天本来的做题记录中就有记录，只不过这里只是把它集合在一起罢了。 CF1438D Powerful Ksenia - 洛谷(同步于 2022.6.14 每日做题记录) CF 的题似乎总是要善于猜结论。 思路分析： 首先考虑一次操作可以做什么： ...
力扣每日一题
【代码】力扣每日一题。
2023秋招笔试题记录-自用
因此对于长度为n的序列，最后的结果只有2种可能。小美定义一个 01 串的权值为：每次操作选择一位取反，使得相邻字符都不相等的最小操作次数。例如，"10001"的权值是 1，因为只需要修改一次：对第三个字符取反即可。一个仅包含’0’和’1’的字符串，长度不超过 2000。长度为 2 的子串中，有 2 个"00"的权值是 1。长度为 3 的 3 个子串权值都是 1。长度为 4 的 2 个子串权值都是 1。长度为 5 的 1 个子串权值是 1。总权值之和为 2+3+2+1=8。所有非空子串的权值和。
逻辑思维训练500题(带答案)前237题 文末附完整版PDF
逻辑思维训练500题，前237题答案，第一章 假设法，第一章 假设法，第三章 排除法，第四章 分析法，第五章 观察法
LeetCode 热题 HOT 100 题解（仅记录解题思路）
由于数组中只有一个元素出现一次，其余元素都出现两次，因此用集合中的元素之和的两倍减去数组中的元素之和，剩下的数就是数组中只出现一次的数字。遍历数组中的每个数字，如果集合中没有该数字，则将该数字加入集合，如果集合中已经有该数字，则将该数字从集合中删除，最后剩下的数字就是只出现一次的数字。指针p1在头结点处，指针p2在头结点的next处，然后p1每次移动1格，p2则走2步，若在其中一个进入null之前两者指向同一结点，则代表有环。使用双指针，左指针指向当前已经处理好的序列的尾部，右指针指向待处理序列的头部。
MS-SQL Server

34,876

社区成员

254,640

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章