之前在
http://topic.csdn.net/u/20090326/08/a6f1ba8d-764c-4917-b267-f115bf535b48.html?174112806
这个贴子里面问的问题,dawugui大大的解答太精彩了,一并解决了我几个问题感动。。
不过因为我们的数据库比较特殊,所以还有一点问题,我们的数据库是这样的
学号 姓名 课程名称 总评成绩 补考成绩
001 张三 ASP 80 NULL
001 张三 数学 50 0
001 张三 语文 45 60
002 李四 ASP 42 60
002 李四 语文 80 NULL
002 李四 语文 80 Null即是如果总评成绩不合格的就要补考,补考成绩也是在同一个表里面的现在我想生成的成绩单是补考后的成绩单,如果补考合格的,就显示60分,补考不合格或缺考的,就是总评成绩大概就是这样,谢谢了
http://topic.csdn.net/u/20090326/08/a6f1ba8d-764c-4917-b267-f115bf535b48.html?174112806
这个贴子里面问的问题,dawugui大大的解答太精彩了,一并解决了我几个问题感动。。
不过因为我们的数据库比较特殊,所以还有一点问题,我们的数据库是这样的
学号 姓名 课程名称 总评成绩 补考成绩
001 张三 ASP 80 NULL
001 张三 数学 50 0
001 张三 语文 45 60
002 李四 ASP 42 60
002 李四 语文 80 NULL
002 李四 语文 80 Null即是如果总评成绩不合格的就要补考,补考成绩也是在同一个表里面的现在我想生成的成绩单是补考后的成绩单,如果补考合格的,就显示60分,补考不合格或缺考的,就是总评成绩大概就是这样,谢谢了
create table reports
(
学号 nvarchar(16),
姓名 nvarchar(16),
课程名称 nvarchar(32),
总评成绩 decimal(4, 1),
补考成绩 decimal(4, 1)
)insert reports
(学号, 姓名, 课程名称, 总评成绩, 补考成绩)
select '001', '张三', 'ASP', 80, NULL
union all select '001', '张三', '数学', 50, 0
union all select '001', '张三', '语文', 45, 60
union all select '002', '李四', 'ASP', 42, 60
union all select '002', '李四', '语文', 80, NULL
union all select '002', '李四', '语文', 80, Null -- select * from reports
select 学号, 姓名, 课程名称,
case when 补考成绩>=60 then 60
else 总评成绩 end
as 成绩
from reports
学号 姓名 课程名称 成绩
---------------- ---------------- -------------------------------- ---------------------------------------
001 张三 ASP 80.0
001 张三 数学 50.0
001 张三 语文 60.0
002 李四 ASP 60.0
002 李四 语文 80.0
002 李四 语文 80.0(6 row(s) affected)
最后一项重复,不知道楼主想怎么处理?
if not object_id('tb') is null
drop table tb
Go
Create table tb([学号] nvarchar(3),[姓名] nvarchar(2),[课程名称] nvarchar(3),[总评成绩] int,[补考成绩] int)
Insert tb
select N'001',N'张三',N'ASP',80,null union all
select N'001',N'张三',N'数学',50,0 union all
select N'001',N'张三',N'语文',45,60 union all
select N'002',N'李四',N'ASP',42,60 union all
select N'002',N'李四',N'语文',80,null union all
select N'002',N'李四',N'语文',80,null
Go
Select * from tbselect 学号 ,
max(case 课程名称 when 'asp' then case when 总评成绩<60 and 补考成绩>=60 then 60 else 总评成绩 end end) [asp],
max(case 课程名称 when '英语' then case when 总评成绩<60 and 补考成绩>=60 then 60 else 总评成绩 end end) [英语],
max(case 课程名称 when '语文' then case when 总评成绩<60 and 补考成绩>=60 then 60 else 总评成绩 end end) [语文],
max(case 课程名称 when '数学' then case when 总评成绩<60 and 补考成绩>=60 then 60 else 总评成绩 end end) [数学]
from tb
group by 学号001 80 NULL 60 50
002 60 NULL 80 NULL
Insert tb
select N'001',N'张三',N'ASP',80,null union all
select N'001',N'张三',N'数学',50,0 union all
select N'001',N'张三',N'语文',45,60 union all
select N'002',N'李四',N'ASP',42,60 union all
select N'002',N'李四',N'语文',80,null union all
select N'002',N'李四',N'语文',80,null
Goselect 学号,姓名,
max(case 课程名称 when 'ASP' then score else 0 end) ASP,
max(case 课程名称 when '数学' then score else 0 end) 数学,
max(case 课程名称 when '语文' then score else 0 end) 语文
from
(
Select 学号,姓名,课程名称,score = (case when 补考成绩 = 0 or 补考成绩 is null then 总评成绩 else 60 end) from tb
) t
group by 学号,姓名
order by 学号,姓名drop table tb/*
学号 姓名 ASP 数学 语文
---- ---- ----------- ----------- -----------
001 张三 80 50 60
002 李四 60 0 80(所影响的行数为 2 行)
*/
Insert tb
select N'001',N'张三',N'ASP',80,null union all
select N'001',N'张三',N'数学',50,0 union all
select N'001',N'张三',N'语文',45,60 union all
select N'002',N'李四',N'ASP',42,60 union all
select N'002',N'李四',N'语文',80,null union all
select N'002',N'李四',N'语文',80,null
Go--sql 2000静态SQL,指课程名称固定
select 学号,姓名,
max(case 课程名称 when 'ASP' then score else 0 end) ASP,
max(case 课程名称 when '数学' then score else 0 end) 数学,
max(case 课程名称 when '语文' then score else 0 end) 语文
from
(
Select 学号,姓名,课程名称,score = (case when 补考成绩 = 0 or 补考成绩 is null then 总评成绩 else 60 end) from tb
) t
group by 学号,姓名
order by 学号,姓名
/*
学号 姓名 ASP 数学 语文
---- ---- ----------- ----------- -----------
001 张三 80 50 60
002 李四 60 0 80(所影响的行数为 2 行)
*/--sql 2000动态SQL,指课程名称不固定
declare @sql varchar(8000)
set @sql = 'select 学号,姓名 '
select @sql = @sql + ' , max(case 课程名称 when ''' + 课程名称 + ''' then score else 0 end) [' + 课程名称 + ']'
from (select distinct 课程名称 from tb) as a
set @sql = @sql + ' from (Select 学号,姓名,课程名称,score = (case when 补考成绩 = 0 or 补考成绩 is null then 总评成绩 else 60 end) from tb) t group by 学号,姓名 order by 学号,姓名'
exec(@sql)
/*
学号 姓名 ASP 数学 语文
---- ---- ----------- ----------- -----------
001 张三 80 50 60
002 李四 60 0 80*/drop table tb
Insert tb
select N'001',N'张三',N'ASP',80,null union all
select N'001',N'张三',N'数学',50,0 union all
select N'001',N'张三',N'语文',45,60 union all
select N'002',N'李四',N'ASP',42,60 union all
select N'002',N'李四',N'语文',80,null union all
select N'002',N'李四',N'语文',80,null
Goselect 学号,姓名,
max(case 课程名称 when 'ASP' then score else 0 end) ASP,
max(case 课程名称 when '数学' then score else 0 end) 数学,
max(case 课程名称 when '语文' then score else 0 end) 语文
from
(
Select 学号,姓名,课程名称,score = case when isnull(补考成绩,0) >= 60 then 60 else 总评成绩 end from tb
) t
group by 学号,姓名
order by 学号,姓名drop table tb学号 姓名 ASP 数学 语文
---- ---- ----------- ----------- -----------
001 张三 80 50 60
002 李四 60 0 80
select
学号,
姓名,
课程名称,
case when 补考成绩<60 then 总评成绩 else 补考成绩 end as 最终成绩
from 成绩表
select
学号,
姓名,
课程名称,
case when isnull(补考成绩,0)<60 then 总评成绩 else 补考成绩 end as 最终成绩
from 成绩表