declare @table_b table(id int,商家 char(4),付款方式 char(3),付款金额 int)
insert into @table_b
select 1, '1001', '001', 100 union
select 2, '1001', '002', 200 union
select 3, '1001', '003', 300 union
select 4, '1002', '001', 150 union
select 5, '1002', '003', 250
select * from @table_bselect id=(select count(distinct 商家) from @table_b where 商家<=a.商家),
商家,总金额=sum(付款金额),
现金=sum(case 付款方式 when '001' then 付款金额 else 0 end),
银行卡=sum(case 付款方式 when '002' then 付款金额 else 0 end),
支票=sum(case 付款方式 when '003' then 付款金额 else 0 end)
from @table_b a
group by 商家
/*结果
1 1001 600 100 200 300
2 1002 400 150 0 250
*/
insert into @table_b
select 1, '1001', '001', 100 union
select 2, '1001', '002', 200 union
select 3, '1001', '003', 300 union
select 4, '1002', '001', 150 union
select 5, '1002', '003', 250
select * from @table_bselect id=(select count(distinct 商家) from @table_b where 商家<=a.商家),
商家,总金额=sum(付款金额),
现金=sum(case 付款方式 when '001' then 付款金额 else 0 end),
银行卡=sum(case 付款方式 when '002' then 付款金额 else 0 end),
支票=sum(case 付款方式 when '003' then 付款金额 else 0 end)
from @table_b a
group by 商家
/*结果
1 1001 600 100 200 300
2 1002 400 150 0 250
*/
解决方案 »
- 请问这样的表单如何做sql查询
- 1.1.1,1.1.2此类序号排序问题
- 关于SQL 2008
- 数据写不进去 ,请教
- restore database遇到"database in use"提示
- 数据复制配置能否脚本化,有相关经验的同僚们帮帮忙,在线等!
- 最近在搞数据库升级,但是修改Table约束出错,请帮忙解决!!
- 这个SQL语句怎么写?
- 怎么能把 oracle 中的 CLOB 字段中的数据导入 sql server
- 求助一个简单的存储过程语句,求大神指点
- 如何进行一个范围的分组,如a列中值是3和4的为一组,其他值为一组,并对分组后的结果进行每组技术。
- 这本书有没有电子版下载?<<中文版 SQL Server 2000 开发与管理应用实例>> 作者:邹建
(
id int IDENTITY (1,1)NOT NULL,
付款方式 varchar(10),
付款金额 varchar(10)
)
insert TABLE_A
select '001','现金' union
select '002','银行卡' union
select '003','支票'create table TABLE_B
(
id int IDENTITY (1,1)NOT NULL,
商家 varchar(10),
付款方式 varchar(10),
付款金额 int
)
insert TABLE_B
select '1001','001',100 union
select '1001','002',200 union
select '1001','003',300 union
select '1002','001',150 union
select '1002','003',250DECLARE @T_SQL as varchar(8000)
DECLARE @T_SQL1 as varchar(8000)
DECLARE @T_SQL2 as varchar(8000)
set @T_SQL2=''
select @T_SQL2=@T_SQL2 + 'T2.' + 付款金额 +',' from TABLE_A
set @T_SQL2=left(@T_SQL2,len(@T_SQL2)-1)
set @T_SQL1=''
select @T_SQL1=@T_SQL1 + 'sum(case when 付款方式=''' + 付款方式 +''' then 付款金额 else 0 end) as ' + 付款金额 +',' from TABLE_A
set @T_SQL1='(select 商家,' + left(@T_SQL1,len(@T_SQL1)-1) + ' from TABLE_B group by 商家) T2 where T1.商家=T2.商家'
set @T_SQL='select T1.商家,T1.总金额,'+ @T_SQL2 + ' From (select 商家,sum(付款金额) as 总金额 from TABLE_B group by 商家) T1,' + @T_SQL1
exec (@T_SQL)
DECLARE @T_SQL as varchar(8000)
DECLARE @T_SQL1 as varchar(8000)
DECLARE @T_SQL2 as varchar(8000)
set @T_SQL2=''
select @T_SQL2=@T_SQL2 + 'T2.' + 付款金额 +',' from TABLE_A
set @T_SQL2=left(@T_SQL2,len(@T_SQL2)-1)
set @T_SQL1=''
select @T_SQL1=@T_SQL1 + 'sum(case when 付款方式=''' + 付款方式 +''' then 付款金额 else 0 end) as ' + 付款金额 +',' from TABLE_A
set @T_SQL1='(select 商家,' + left(@T_SQL1,len(@T_SQL1)-1) + ' from TABLE_B group by 商家) T2 where T1.商家=T2.商家'
set @T_SQL='select (select count(*) from (select 商家 from TABLE_B group by 商家) S where S.商家<=T1.商家) as id, T1.商家,T1.总金额,'+ @T_SQL2 + ' From (select 商家,sum(付款金额) as 总金额 from TABLE_B group by 商家) T1,' + @T_SQL1
exec (@T_SQL)
create table TABLE_A
(
id int IDENTITY (1,1)NOT NULL,
付款方式 varchar(10),
付款金额 varchar(10)
)
insert TABLE_A
select '001','现金' union
select '002','银行卡' union
select '003','支票'create table TABLE_B
(
id int IDENTITY (1,1)NOT NULL,
商家 varchar(10),
付款方式 varchar(10),
付款金额 int
)
insert TABLE_B
select '1001','001',100 union
select '1001','002',200 union
select '1001','003',300 union
select '1002','001',150 union
select '1002','003',250
go
--处理语句
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+','+付款金额+'=sum(case 付款方式 when '''+付款方式+''' then 付款金额 else 0 end)'
from table_a
exec('select id=(select count(distinct 商家) from table_b where 商家<=a.商家),
商家,总金额=sum(付款金额)'+@sql+' from table_b a group by 商家')
/*结果
id 商家 总金额 现金 银行卡 支票
1 1001 600 100 200 300
2 1002 400 150 0 250*/
rivery(river)的想法好。