求一个复杂的SQL语句?
已知表1 paycode name
1 现金
2 刷卡
3 支票
表2 paycode total date
1 30.00 2007-4-23
2 40.00 2007-4-24
1 30.00 2007-4-24
3 20.00 2007-4-25
2 50.00 2007-4-25
3 10.00 2007-4-25
如果要查某一天或某段时间每种支付方式的收入,SQL语句应该怎么写，才能得到以下的结果(其中支付方式是从表一里取出的,不是固定的).先谢谢各位了
日期 现金 刷卡 支票 合计
2007-4-23 30.00 0.00 0.00 30.00
2007-4-24 30.00 40.00 0.00 70.00
2007-4-25 0.00 50.00 30.00 80.00
合计: 60.00 90.00 30.00 180.00
[解决办法]
--静态
select case grouping(date) when 1 then '合计: ' else convert(varchar(10),date,120) end 日期,
sum(case paycode when 1 then total else 0 end) 现金,
sum(case paycode when 2 then total else 0 end) 刷卡,
sum(case paycode when 3 then total else 0 end) 支票,
sum(total) 合计
from 表2 group by date with rollup
[解决办法]
--动态
set nocount on
create table agg(paycode int, name varchar(5))
insert agg select 1 , '现金 '
union all select 2 , '刷卡 '
union all select 3 , '支票 '
create table aggs(paycode int,total float,[date] smalldatetime)
insert aggs select 1 ,30.00 , '2007-4-23 '
union all select 2 ,40.00 , '2007-4-24 '
union all select 1 ,30.00 , '2007-4-24 '
union all select 3 ,20.00 , '2007-4-25 '
union all select 2 ,50.00 , '2007-4-25 '
union all select 3 ,10.00 , '2007-4-25 '
declare @a varchar(2000),@b varchar(2000),@c varchar(2000)
set @a= 'select [日期], '
set @b= ' '
set @c= ' '
select name,total,convert(varchar(10),[date],120) [日期] into GG from agg a Inner Join aggs b On a.paycode=b.paycode
select @a=@a+ ' sum(case when name= ' ' '+name+ ' ' ' then total else 0 end) '+name+ ', ',@b=@b+ ' sum(case when name= ' ' '+name+ ' ' ' then total else 0 end) + ',@c=@c+ 'sum( '+name+ '), ' from GG group by name
select @a=left(@a,len(@a)-1)
select @b=left(@b,len(@b)-1)
select @c=left(@c,len(@c)-1)
exec(@a+ ', '+@b+ ' 合计 into YY from GG group by [日期] ')
exec( 'select * from YY Union all select ' '合计 ' ', '+@c+ ',sum(合计) from YY ')
drop table GG,YY
[解决办法]
--动态
create table 表1 (paycode int,name varchar(16))
insert into 表1 select
1 , '现金 ' union all select
2 , '刷卡 ' union all select
3 , '支票 '
create table 表2 (paycode int,total decimal(19,2),date datetime)
insert into 表2 select
1, 30.00, '2007-4-23 ' union all select
2, 40.00, '2007-4-24 ' union all select
1, 30.00, '2007-4-24 ' union all select
3, 20.00, '2007-4-25 ' union all select
2, 50.00, '2007-4-25 ' union all select
3, 10.00, '2007-4-25 '
declare @strsql varchar(8000)
set @strsql= ' '
select @strsql=@strsql+ 'sum(case paycode when '+cast(paycode as varchar(8))+ ' then total else 0 end) '+name+ ', ' from 表1
exec( 'select case grouping(date) when 1 then ' '合计: ' ' else convert(varchar(10),date,120) end 日期, '+@strsql+ 'sum(total) 合计 from 表2 group by date with rollup ')
日期 现金 刷卡 支票 合计
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
2007-04-23 30.00 .00 .00 30.00
2007-04-24 30.00 40.00 .00 70.00
2007-04-25 .00 50.00 30.00 80.00
合计: 60.00 90.00 30.00 180.00