面试题
科目
id 科目名称
1语文
2数学
3物理
4化学
5英语
学生
id 姓名 性别
1张三男
2刘二男
3梅子女
4王三男
5飞鱼男
成绩表
学生id 科目id 成绩
1185
1255
1375
1490
1588
2165
2288
2398
2478
2589
3145
3267
3390
3443
4188
4266
4478
5198
5260
5378
5488
求每个科目的最高分、最低分，如下格式。
——姓名 科目 最高分 最低分
[解决办法]
SELECT 姓名 ,
科目名称 ,
MAX(成绩) 最高分 ,
MIN(成绩) 最低分
FROM 学生 a
INNER JOIN 成绩表 b ON a.id = b.学生id
INNER JOIN 科目 c ON B.科目id = c.id
GROUP BY 姓名 ,
科目名称
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-22 13:58:55
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[科目]
if object_id('[科目]') is not null drop table [科目]
go
create table [科目]([id] int,[科目名称] varchar(4))
insert [科目]
select 1,'语文' union all
select 2,'数学' union all
select 3,'物理' union all
select 4,'化学' union all
select 5,'英语'
--> 测试数据：[学生]
if object_id('[学生]') is not null drop table [学生]
go
create table [学生]([id] int,[姓名] varchar(4),[性别] varchar(2))
insert [学生]
select 1,'张三','男' union all
select 2,'刘二','男' union all
select 3,'梅子','女' union all
select 4,'王三','男' union all
select 5,'飞鱼','男'
--> 测试数据：[成绩表]
if object_id('[成绩表]') is not null drop table [成绩表]
go
create table [成绩表]([学生id] int,[科目id] int,[成绩] int)
insert [成绩表]
select 1,1,85 union all
select 1,2,55 union all
select 1,3,75 union all
select 1,4,90 union all
select 1,5,88 union all
select 2,1,65 union all
select 2,2,88 union all
select 2,3,98 union all
select 2,4,78 union all
select 2,5,89 union all
select 3,1,45 union all
select 3,2,67 union all
select 3,3,90 union all
select 3,4,43 union all
select 4,1,88 union all
select 4,2,66 union all
select 4,4,78 union all
select 5,1,98 union all
select 5,2,60 union all
select 5,3,78 union all
select 5,4,88
go
;with t
as(
select
px=row_number()over(partition by a.科目名称 order by c.成绩 desc),
b.姓名,
a.科目名称,
c.成绩
from
[科目] a,
[学生] b,
[成绩表] c
where
a.id=c.科目id and b.id=c.学生id
)
select
姓名,
科目名称,
成绩 as 最高分
from
t
where
px=1
/*
姓名 科目名称 最高分
---- ---- -----------
张三 化学 90
刘二 数学 88
刘二 物理 98
刘二 英语 89
飞鱼 语文 98
(5 行受影响)
*/
如果还有最低分 你的名字怎么处理 没说清楚
