表结构
表名:学生名册
学号 姓名 班级
20050001 张三 1班
20050002 李四 2班表名:学生缴费
学号 缴费金额 学期
20050001 500 2006-2007-2
20050001 400 2006-2007-1
20050002 300 2006-2007-2表名:学生领书
学号 教材名称 单价 学期
20050001 软件工程 25 2006-2007-2
20050001 photoshop教程 35 2006-2007-2
20050002 coreldraw教程 30 2006-2007-1
20050001 coreldraw教程 30 2006-2007-1
实现目标:想按学期给学生结算费用,想达到下面这种格式(这是我想的格式,其它的也可以哈)?小弟万分感激!!表名:学生退费
学号 学期 缴纳 支出 退费
20050001 2006-2007-1 400 30 370
20050001 2006-2007-2 500 60 440
from 学生名册 a
left join 学生缴费 b
on a.学号=b.学号
left join 学生领书 c
on b.学号=c.学号 and b.学期=c.学期
group by a.学号,b.学期,b.缴费金额
declare @学生名册 table(学号 int,姓名 varchar(4),班级 varchar(4))
insert @学生名册
select 20050001,'张三','1班 ' union all
select 20050002,'李四','2班 '
--原始数据:@学生缴费
declare @学生缴费 table(学号 int,缴费金额 int,学期 varchar(12))
insert @学生缴费
select 20050001,500,'2006-2007-2 ' union all
select 20050001,400,'2006-2007-1 ' union all
select 20050002,300,'2006-2007-2 '
--原始数据:@学生领书
declare @学生领书 table(学号 int,教材名称 varchar(13),单价 int,学期 varchar(12))
insert @学生领书
select 20050001,'软件工程',25,'2006-2007-2 ' union all
select 20050001,'photoshop教程',35,'2006-2007-2 ' union all
select 20050002,'coreldraw教程',30,'2006-2007-1 ' union all
select 20050001,'coreldraw教程',30,'2006-2007-1 'select a.学号,b.学期,缴纳=b.缴费金额,支出=sum(c.单价),退费=b.缴费金额-sum(c.单价)
from @学生名册 a
left join @学生缴费 b
on a.学号=b.学号
left join @学生领书 c
on b.学号=c.学号 and b.学期=c.学期
group by a.学号,b.学期,b.缴费金额/*
学号 学期 缴纳 支出 退费
20050001 2006-2007-1 400 30 370
20050001 2006-2007-2 500 60 440
20050002 2006-2007-2 300 NULL NULL
*/
(select 学号 , 学期 , sum(缴费金额) 缴纳 from 学生缴费 group by 学号 , 学期) t1
left join (select 学号 , 学期 , sum(单价) 支出 from 学生领书 group by 学号 , 学期) t2
on t1.学号 = t2.学号 and t1.学期 = t2.学期
insert into 学生名册 values('20050001', '张三', '1班')
insert into 学生名册 values('20050002', '李四', '2班')
create table 学生缴费(学号 varchar(10),缴费金额 int, 学期 varchar(20))
insert into 学生缴费 values('20050001', 500, '2006-2007-2')
insert into 学生缴费 values('20050001', 400, '2006-2007-1')
insert into 学生缴费 values('20050002', 300, '2006-2007-2')
create table 学生领书(学号 varchar(10),教材名称 varchar(20),单价 int,学期 varchar(20))
insert into 学生领书 values('20050001', '软件工程' , 25, '2006-2007-2')
insert into 学生领书 values('20050001', 'photoshop教程', 35, '2006-2007-2')
insert into 学生领书 values('20050002', 'coreldraw教程', 30, '2006-2007-1')
goselect isnull(t1.学号,t2.学号) 学号, isnull(t1.学期,t2.学期) 学号, isnull(t1.缴纳,0) 缴纳 , isnull(t2.支出,0) 支出 , isnull(t1.缴纳,0) - isnull(t2.支出,0) 退费 from
(select 学号 , 学期 , sum(缴费金额) 缴纳 from 学生缴费 group by 学号 , 学期) t1
full join (select 学号 , 学期 , sum(单价) 支出 from 学生领书 group by 学号 , 学期) t2
on t1.学号 = t2.学号 and t1.学期 = t2.学期drop table 学生名册,学生缴费,学生领书/*
学号 学号 缴纳 支出 退费
---------- -------------------- ----------- ----------- -----------
20050001 2006-2007-1 400 0 400
20050001 2006-2007-2 500 60 440
20050002 2006-2007-1 0 30 -30
20050002 2006-2007-2 300 0 300(所影响的行数为 4 行)
*/
declare @a table(学号 bigint,姓名 varchar(20),班级 varchar(20))
insert @a
select 20050001, '张三', '1班'
union all
select 20050002, '李四', '2班'declare @b table(学号 bigint,缴费金额 int,学期 varchar(20))
insert @b
select 20050001, 500 , '2006-2007-2'
union all
select 20050001, 400, '2006-2007-1'
union all
select 20050002, 300, '2006-2007-2'
declare @c table(学号 bigint,教材名称 varchar(20),单价 int,学期 varchar(20))
insert @c
select 20050001, '软件工程', 25 , '2006-2007-2'
union all
select 20050001, 'photoshop教程', 35, '2006-2007-2'
union all
select 20050002, 'coreldraw教程' , 30, '2006-2007-1'
union all
select 20050001, 'coreldraw教程' , 30 , '2006-2007-1'select 学号,支出,学期,缴费金额,缴费金额-支出 退费 from (
select 学号,sum(单价) as 支出,学期,缴费金额 from
(
select distinct a.学号,b.缴费金额, c.单价,c.学期 from @a a,@b b,@c c where a.学号=b.学号 and b.学号=c.学号 and b.学期=c.学期group by c.学期,a.学号,b.缴费金额, c.单价) dgroup by 学期,学号,缴费金额
) s/*(所影响的行数为 2 行)
(所影响的行数为 3 行)
(所影响的行数为 4 行)学号 支出 学期 缴费金额 退费
-------------------- ----------- -------------------- ----------- -----------
20050001 30 2006-2007-1 400 370
20050001 60 2006-2007-2 500 440(所影响的行数为 2 行)
*/