求一个SQL语句,如何获得每个班的分数第二名的姓名

jane0228 2008-11-28 05:39:42
成绩表 A :学号(Number)<主键>、成绩(Score)
学生表 B: 学号(Number)<主键>、姓名(Name)、班级(Class)
求出每个班的分数第二名的姓名。
...全文
1097 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
jane0228 2008-11-30
  • 打赏
  • 举报
回复
第一次发贴,谢谢大家的帮忙了哈~
jimoshatan 2008-11-29
  • 打赏
  • 举报
回复
CREATE TABLE #A (S_NO VARCHAR(10),S_NUM INT)
INSERT INTO #A SELECT 101,80
INSERT INTO #A SELECT 102,65
INSERT INTO #A SELECT 103,80
INSERT INTO #A SELECT 104,75
INSERT INTO #A SELECT 105,95
INSERT INTO #A SELECT 106,88
INSERT INTO #A SELECT 107,90

CREATE TABLE #B (S_NO VARCHAR(10),S_NAME VARCHAR(10),S_CLASS VARCHAR(10))
INSERT INTO #B SELECT 101,'A','A01'
INSERT INTO #B SELECT 102,'B','A01'
INSERT INTO #B SELECT 103,'C','A01'
INSERT INTO #B SELECT 104,'D','A02'
INSERT INTO #B SELECT 105,'E','A02'
INSERT INTO #B SELECT 106,'F','A02'
INSERT INTO #B SELECT 107,'G','A01'


SELECT A.S_NO,B.S_NAME,B.S_CLASS,A.S_NUM INTO #C FROM #A A LEFT JOIN #B B ON A.S_NO=B.S_NO

SELECT *
FROM #C A
WHERE (SELECT COUNT(DISTINCT S_NUM) FROM #C WHERE S_CLASS=A.S_CLASS AND S_NUM<A.S_NUM)=1


DROP TABLE #A,#B,#C
ljhcy99 2008-11-28
  • 打赏
  • 举报
回复
select 姓名
from
(
select 姓名,Row_number()over(partition by 班级,学号 order by 成绩) as num
from
(select 学生表.学号 as 学号 ,学生表.班级 as 班级, 学生表.姓名 as 姓名, 成绩表.成绩 as 成绩
from 成绩表,学生表
where 学生表.学号=成绩表.学号 ) as AA ) AS Tab

where num = 2
中国风 2008-11-28
  • 打赏
  • 举报
回复
select
*
from
(select a.Number,b.Name,b.Class,dense_rank()over(partition by b.Class order by a.Score desc) num from A join B on a.Number=b.Number)t
where
num=2


05用rank/dense_rank區分並列排名
s_hluo 2008-11-28
  • 打赏
  • 举报
回复



select Name from B join A on B.Number = A.Number where A.Score = (
select top 1 Score from (select distinct top 2 Score from A order by Score desc) as T order by Score
)
jimoshatan 2008-11-28
  • 打赏
  • 举报
回复
分数有相同的时候,全部出来。没有相同的就出一条
jimoshatan 2008-11-28
  • 打赏
  • 举报
回复
CREATE TABLE #A (S_NO VARCHAR(10),S_NUM INT)
INSERT INTO #A SELECT 101,80
INSERT INTO #A SELECT 102,65
INSERT INTO #A SELECT 103,80
INSERT INTO #A SELECT 104,75
INSERT INTO #A SELECT 105,95
INSERT INTO #A SELECT 106,88
INSERT INTO #A SELECT 107,90

CREATE TABLE #B (S_NO VARCHAR(10),S_NAME VARCHAR(10),S_CLASS VARCHAR(10))
INSERT INTO #B SELECT 101,'A','A01'
INSERT INTO #B SELECT 102,'B','A01'
INSERT INTO #B SELECT 103,'C','A01'
INSERT INTO #B SELECT 104,'D','A02'
INSERT INTO #B SELECT 105,'E','A02'
INSERT INTO #B SELECT 106,'F','A02'
INSERT INTO #B SELECT 107,'G','A01'


SELECT A.S_NO,B.S_NAME,B.S_CLASS,A.S_NUM INTO #C FROM #A A LEFT JOIN #B B ON A.S_NO=B.S_NO

SELECT * FROM #C A
WHERE (SELECT COUNT(*) FROM #C WHERE S_CLASS=A.S_CLASS AND S_NUM>A.S_NUM)<2
AND EXISTS (SELECT 1 FROM #C WHERE S_CLASS=A.S_CLASS AND S_NUM>A.S_NUM )


DROP TABLE #A,#B,#C


/**
S_NO S_NAME S_CLASS S_NUM
---------- ---------- ---------- -----------
101 A A01 80
103 C A01 80
106 F A02 88
-狙击手- 2008-11-28
  • 打赏
  • 举报
回复
select tb.Number,tb.Name,tb.Class,ta.Score
from a as ta
left join b as tb
on ta.Number = tb.Number
where (select count(1) from a,b where a.number = b.number and b.Class= tb.Class and a.Score >=ta.Score) = 2
csdyyr 2008-11-28
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 csdyyr 的回复:]
引用 3 楼 csdyyr 的回复:
SQL codeselectdistinctnamefromajoinbona.number=b.numberwhereScorein(selecttop2Scorefromawherenumber=b.numberorderbyScoredesc)

SQL codeselectdistinctnamefromajoinbona.number=b.numberwhere(selectcount(distinctScore)fromawherenumber=a.numberandScore>a.Score)=1
[/Quote]

--别名错了
select distinct name
from a as t join b on t.number=b.number
where (select count(distinct Score) from a where number=t.number and Score>t.Score)=1
csdyyr 2008-11-28
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 csdyyr 的回复:]
SQL codeselectdistinctnamefromajoinbona.number=b.numberwhereScorein(selecttop2Scorefromawherenumber=b.numberorderbyScoredesc)
[/Quote]

select distinct name
from a join b on a.number=b.number
where (select count(distinct Score) from a where number=a.number and Score>a.Score)=1
csdyyr 2008-11-28
  • 打赏
  • 举报
回复

select distinct name
from a join b on a.number=b.number
where Score in (select top 2 Score from a where number=b.number order by Score desc)
zhouxu_hust 2008-11-28
  • 打赏
  • 举报
回复
.....
-狙击手- 2008-11-28
  • 打赏
  • 举报
回复
2005 ?

34,838

社区成员

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

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