问题描述

sql语句 多条件查询求解答

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

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

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

解决方案

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

解决方案二：

创建表：
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