日期:2014-05-17 浏览次数:20540 次
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