declare @sql varchar(4000) set @sql='select a.学号 , a.姓名 ,a.性别 ,a.系别' select @sql=@sql+',['+费用名称+']=sum(case c.费用代码 when ''' +费用代码+''' then c.金额 else 0 end)' from 表二 set @sql=@sql+',[合计]=sum(c.金额) from 表一 a , 表二 b, 表三 c where a.学号=b.学号 and b.费用代码=c.费用代码' set @sql=@sql+' group by a.学号 , a.姓名 ,a.性别 ,a.系别' exec(@sql)
/*想得到的结果是: 学号 姓名 性别 系别 学费 书费 住宿费 合计 001 张三 男 计算机 5000 500 800 6300 002 李四 男 经管系 3500 500 500 4500 003 王五 女 外语系 3500 500 1000 5000 */create table 表一(学号 varchar(8),姓名 varchar(10), 性别 char(2), 系别 varchar(40)) create table 表二(费用代码 varchar(10), 费用名称 varchar(20)) create table 表三(学号 varchar(8),费用代码 varchar(10), 金额 money) insert into 表一 select '0001','张三' , '男','计算机' union all select '0002', '李四' , '男' , '经管系' union all select '0003','王五' , '女' , '外语系' insert into 表二 select 'XF','学费' union all select 'SF','书费' union all select 'ZSF','住宿费' insert into 表三 select '0001','XF',5000 union all select '0001','SF',500 union all select '0001','ZSF',800 union all select '0002','XF',3500 union all select '0002','SF',500 union all select '0002','ZSF',500 union all select '0003','XF',3500 union all select '0003','SF',500 union all select '0003','ZSF',1000 declare @sql varchar(4000) set @sql='select a.学号 , a.姓名 ,a.性别 ,a.系别' select @sql=@sql+',['+费用名称+']=sum(case c.费用代码 when ''' +费用代码+''' then c.金额 else 0 end)' from 表二 set @sql=@sql+',[合计]=sum(c.金额) from 表一 a , 表二 b, 表三 c where a.学号=c.学号 and b.费用代码=c.费用代码' set @sql=@sql+' group by a.学号 , a.姓名 ,a.性别 ,a.系别' exec(@sql)drop table 表一,表二,表三
create table t1(xh varchar(5),xm varchar(10),xb varchar(2),xib varchar(10)) create table t2(fycode varchar(10),fyname varchar(20)) create table t3(xh varchar(5),fycode varchar(10),je money) insert into t1 values('0001','张三', '男', '计算机') insert into t1 values('0002','李四', '男', '经管系') insert into t1 values('0003','王五', '女', '外语系')insert into t2 values('XF','学费') insert into t2 values('SF','书费') insert into t2 values('ZSF','住宿费')insert into t3 values('0001','XF',5000) insert into t3 values('0001','SF',500) insert into t3 values('0001','ZSF',800) insert into t3 values('0002','XF',3500) insert into t3 values('0002','SF',500) insert into t3 values('0002','ZSF',500) insert into t3 values('0003','XF',3500) insert into t3 values('0003','SF',500) insert into t3 values('0003','ZSF',1000)select * from t1 select * from t2 select * from t3select a.xh,a.xm,a.xb,a.xib,c.je,d.je,e.je,f.je from ((select xh,xm,xb,xib from t1) as a left join (select xh,je from t3 where fycode='XF') as c on a.xh=c.xh left join (select xh,je from t3 where fycode='SF') as d on a.xh=d.xh left join (select xh,je from t3 where fycode='ZSF') as e on a.xh=e.xh left join (select xh,sum(je) as je from t3 group by xh) as f on a.xh=f.xh)drop table t1 drop table t2 drop table t3
set @sql='select a.学号 , a.姓名 ,a.性别 ,a.系别'
select @sql=@sql+',['+费用名称+']=sum(case c.费用代码 when '''
+费用代码+''' then c.金额 else 0 end)'
from 表二
set @sql=@sql+',[合计]=sum(c.金额) from 表一 a , 表二 b, 表三 c where a.学号=b.学号 and b.费用代码=c.费用代码'
set @sql=@sql+' group by a.学号 , a.姓名 ,a.性别 ,a.系别'
exec(@sql)
学号 姓名 性别 系别 学费 书费 住宿费 合计
001 张三 男 计算机 5000 500 800 6300
002 李四 男 经管系 3500 500 500 4500
003 王五 女 外语系 3500 500 1000 5000 */create table 表一(学号 varchar(8),姓名 varchar(10), 性别 char(2), 系别 varchar(40))
create table 表二(费用代码 varchar(10), 费用名称 varchar(20))
create table 表三(学号 varchar(8),费用代码 varchar(10), 金额 money)
insert into 表一
select '0001','张三' , '男','计算机' union all
select '0002', '李四' , '男' , '经管系' union all
select '0003','王五' , '女' , '外语系'
insert into 表二
select 'XF','学费' union all
select 'SF','书费' union all
select 'ZSF','住宿费'
insert into 表三
select '0001','XF',5000
union all select '0001','SF',500
union all select '0001','ZSF',800
union all select '0002','XF',3500
union all select '0002','SF',500
union all select '0002','ZSF',500
union all select '0003','XF',3500
union all select '0003','SF',500
union all select '0003','ZSF',1000 declare @sql varchar(4000)
set @sql='select a.学号 , a.姓名 ,a.性别 ,a.系别'
select @sql=@sql+',['+费用名称+']=sum(case c.费用代码 when '''
+费用代码+''' then c.金额 else 0 end)'
from 表二
set @sql=@sql+',[合计]=sum(c.金额) from 表一 a , 表二 b, 表三 c where a.学号=c.学号 and b.费用代码=c.费用代码'
set @sql=@sql+' group by a.学号 , a.姓名 ,a.性别 ,a.系别'
exec(@sql)drop table 表一,表二,表三
create table t1(xh varchar(5),xm varchar(10),xb varchar(2),xib varchar(10))
create table t2(fycode varchar(10),fyname varchar(20))
create table t3(xh varchar(5),fycode varchar(10),je money)
insert into t1 values('0001','张三', '男', '计算机')
insert into t1 values('0002','李四', '男', '经管系')
insert into t1 values('0003','王五', '女', '外语系')insert into t2 values('XF','学费')
insert into t2 values('SF','书费')
insert into t2 values('ZSF','住宿费')insert into t3 values('0001','XF',5000)
insert into t3 values('0001','SF',500)
insert into t3 values('0001','ZSF',800)
insert into t3 values('0002','XF',3500)
insert into t3 values('0002','SF',500)
insert into t3 values('0002','ZSF',500)
insert into t3 values('0003','XF',3500)
insert into t3 values('0003','SF',500)
insert into t3 values('0003','ZSF',1000)select * from t1
select * from t2
select * from t3select a.xh,a.xm,a.xb,a.xib,c.je,d.je,e.je,f.je from
((select xh,xm,xb,xib from t1) as a
left join
(select xh,je from t3 where fycode='XF') as c
on a.xh=c.xh
left join
(select xh,je from t3 where fycode='SF') as d
on a.xh=d.xh
left join
(select xh,je from t3 where fycode='ZSF') as e
on a.xh=e.xh
left join
(select xh,sum(je) as je from t3 group by xh) as f
on a.xh=f.xh)drop table t1
drop table t2
drop table t3