2 shishuoxx shishuoxx 于 2014.03.14 17:37 提问

sql语句 多条件查询求解答

看了半天子条件查询感觉搞不定

现在有一张学生表,里面有字段 学生编号,学生名字,成绩,试卷

请问如何能够查询到 所有试卷不重复,每张试卷成绩最好学生的信息

2个回答

lorewolf311
lorewolf311   2014.03.14 18:18

select 试卷,max(成绩),学生名字 from 学生 group by 试卷

qingzhishuishou
qingzhishuishou   2014.03.14 18:21

创建表:
CREATE TABLE [dbo].scores NOT NULL,
[name] varchar NULL,
[score] [int] NULL,
[paper] varchar NULL,
CONSTRAINT [PK_scores] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
sql:
SELECT (SELECT TOP 1 id FROM scores scc WHERE scc.paper=scores.paper ORDER BY scc.score DESC) AS bianhao,
(SELECT TOP 1 name FROM scores scc WHERE scc.paper=scores.paper ORDER BY scc.score DESC) AS NAME,
(SELECT MAX(score) FROM scores scc WHERE scc.paper=scores.paper) AS score,dbo.scores.paper
FROM dbo.scores GROUP BY paper

shishuoxx
shishuoxx 非常感谢您!
3 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片