现有两张表:
表A:支付方式
字段如下:
支付ID,支付名称
内如大致如下:
1 刷卡
2 现金
3 挂账
此表是用户可以配置的,即数量和名称都不确定表B:支付信息
字段如下:
支付信息ID,支付方式ID,支付金额,支付时间(DateTime)
内容大致如下:
1 1 100 2011-10-09 11:28:16.780
2 1 50 2011-10-09 11:38:19.153
3 2 200 2011-10-09 13:28:16.780
4 2 100 2011-10-15 11:28:16.780
5 2 50 2011-10-15 11:38:19.153
6 3 200 2011-10-15 13:28:16.780现在需要按照日期(每天)统计当天的各种支付方式的支付金额
大致如下表:
支付时间 刷卡金额 现金金额 挂账金额
2011-10-09 150 200 0
2011-10-15 0 150 200如果用户增加或者删除了支付方式表中的选项,生成的表中也要能增加或者删除对应的选项昨天发帖问了下,自己也研究了下
SELECT CONVERT(varchar(10),[支付时间],120),
SUM(CASE 支付方式ID WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE 支付方式ID WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE 支付方式ID WHEN 3 THEN Amount ELSE 0 END) AS Q3
FROM TB
GROUP BY CONVERT(varchar(10),[支付时间],120),
GO
这样基本能实现查出需要的格式的表,但是问题是这样查只能是知道固定的支付方式后生成固定的列数的表格
没法动态改变支付方式的种类,请各位帮忙,怎么才能实现我的需求
表A:支付方式
字段如下:
支付ID,支付名称
内如大致如下:
1 刷卡
2 现金
3 挂账
此表是用户可以配置的,即数量和名称都不确定表B:支付信息
字段如下:
支付信息ID,支付方式ID,支付金额,支付时间(DateTime)
内容大致如下:
1 1 100 2011-10-09 11:28:16.780
2 1 50 2011-10-09 11:38:19.153
3 2 200 2011-10-09 13:28:16.780
4 2 100 2011-10-15 11:28:16.780
5 2 50 2011-10-15 11:38:19.153
6 3 200 2011-10-15 13:28:16.780现在需要按照日期(每天)统计当天的各种支付方式的支付金额
大致如下表:
支付时间 刷卡金额 现金金额 挂账金额
2011-10-09 150 200 0
2011-10-15 0 150 200如果用户增加或者删除了支付方式表中的选项,生成的表中也要能增加或者删除对应的选项昨天发帖问了下,自己也研究了下
SELECT CONVERT(varchar(10),[支付时间],120),
SUM(CASE 支付方式ID WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE 支付方式ID WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE 支付方式ID WHEN 3 THEN Amount ELSE 0 END) AS Q3
FROM TB
GROUP BY CONVERT(varchar(10),[支付时间],120),
GO
这样基本能实现查出需要的格式的表,但是问题是这样查只能是知道固定的支付方式后生成固定的列数的表格
没法动态改变支付方式的种类,请各位帮忙,怎么才能实现我的需求
insert into t1 select 1,'刷卡'
insert into t1 select 2,'现金'
insert into t1 select 3,'挂账'
create table t2(支付信息ID int,支付方式ID int,支付金额 int,支付时间 DateTime)
insert into t2 select 1,1,100,'2011-10-09 11:28:16.780'
insert into t2 select 2,1,50,'2011-10-09 11:38:19.153'
insert into t2 select 3,2,200,'2011-10-09 13:28:16.780'
insert into t2 select 4,2,100,'2011-10-15 11:28:16.780'
insert into t2 select 5,2,50,'2011-10-15 11:38:19.153'
insert into t2 select 6,3,200,'2011-10-15 13:28:16.780'
go
declare @s nvarchar(max),@s1 nvarchar(max)
--获得列标头[a],[b]
select @s=isnull(@s+',','')+'sum(['+ltrim(支付id)+ ']) as ['+ 支付名称 +'金额]',@s1=isnull(@s1+',','')+'['+ltrim(支付id)+']' from t1
exec('select dt as 支付时间,'+@s+' from(select convert(varchar(10),支付时间,120)dt,'+@s1+' from t2 pivot(sum(支付金额) for 支付方式ID in('+@s1+'))b)t group by dt')
/*
支付时间 刷卡金额 现金金额 挂账金额
---------- ----------- ----------- -----------
2011-10-09 150 200 NULL
2011-10-15 NULL 150 200
警告: 聚合或其他 SET 操作消除了 Null 值。(2 行受影响)*/go
drop table t1,t2
exec('select dt as 支付时间,'+@s+' from(select convert(varchar(10),支付时间,120)dt,'+@s1+' from t2 pivot(sum(支付金额) for 支付方式ID in('+@s1+'))b)t group by dt')
insert into t1 select 1,'刷卡'
insert into t1 select 2,'现金'
insert into t1 select 3,'挂账'
create table t2(支付信息ID int,支付方式ID int,支付金额 int,支付时间 DateTime)
insert into t2 select 1,1,100,'2011-10-09 11:28:16.780'
insert into t2 select 2,1,50,'2011-10-09 11:38:19.153'
insert into t2 select 3,2,200,'2011-10-09 13:28:16.780'
insert into t2 select 4,2,100,'2011-10-15 11:28:16.780'
insert into t2 select 5,2,50,'2011-10-15 11:38:19.153'
insert into t2 select 6,3,200,'2011-10-15 13:28:16.780'
go---2000declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),b.支付时间,120) as 支付时间 '
select @sql = @sql + ' , max(case 支付名称 when ''' + 支付名称 + ''' then 支付金额 else 0 end) [' + 支付名称 + '金额]'
from (select distinct 支付名称 from t1) as a
set @sql = @sql + ' from t1 a join t2 b on a.支付ID=b.支付方式ID group by convert(varchar(10),b.支付时间,120)'
exec(@sql) drop table t1,t2
/*支付时间 挂账金额 刷卡金额 现金金额
---------- ----------- ----------- -----------
2011-10-09 0 100 200
2011-10-15 200 0 100(2 行受影响)
*/