create table Hou (hou_id int,m_b_l varchar(10),PAYER int)
insert hou select 1,'NY0001',1
union select 2,'NY0001',1
union select 3,'NY0002',1
union select 4,'NY0002',2
create table Finance(Finance_id int,hou_id int,freight int,currency_id int)
insert finance select 1,1,10,1
union select 2,1,20,2
union select 3,1,30,3
union select 4,2,30,1
union select 5,2,30,2
union select 6,3,30,1
union select 7,4,30,1
create table Currency (currency_id int,currency varchar(10))
insert currency select 1,'RMB'
union select 2,'USD'
union select 3,'EUR'
union select 4,'CAD' declare @s varchar(1000),@s1 varchar(1000)
set @s = ''
select @s = @s + ',sum(case when currency_id = '+rtrim(currency_id)+' then freight else 0 end) as '+currency,
from currency where currency_id in(select currency_id from Finance)
set @s = 'select payer,m_b_l '+@s+' from Hou a,finance b'
set @s = @s+' where a.hou_id = b.hou_id group by payer,m_b_l order by payer,m_b_l'
exec(@s)
/*
payer m_b_l RMB USD EUR
----------- ---------- ----------- ----------- -----------
1 NY0001 40 50 30
1 NY0002 30 0 0
2 NY0002 30 0 0
*/
insert hou select 1,'NY0001',1
union select 2,'NY0001',1
union select 3,'NY0002',1
union select 4,'NY0002',2
create table Finance(Finance_id int,hou_id int,freight int,currency_id int)
insert finance select 1,1,10,1
union select 2,1,20,2
union select 3,1,30,3
union select 4,2,30,1
union select 5,2,30,2
union select 6,3,30,1
union select 7,4,30,1
create table Currency (currency_id int,currency varchar(10))
insert currency select 1,'RMB'
union select 2,'USD'
union select 3,'EUR'
union select 4,'CAD' declare @s varchar(1000),@s1 varchar(1000)
set @s = ''
select @s = @s + ',sum(case when currency_id = '+rtrim(currency_id)+' then freight else 0 end) as '+currency,
from currency where currency_id in(select currency_id from Finance)
set @s = 'select payer,m_b_l '+@s+' from Hou a,finance b'
set @s = @s+' where a.hou_id = b.hou_id group by payer,m_b_l order by payer,m_b_l'
exec(@s)
/*
payer m_b_l RMB USD EUR
----------- ---------- ----------- ----------- -----------
1 NY0001 40 50 30
1 NY0002 30 0 0
2 NY0002 30 0 0
*/
表 Hou ,Finance,currency在数据库中已经存在,现在我的是拉某一个payer的统计信息,
假如我要payer为1的信息,他里面有三种币别,就罗列三种币别,如果我要payer为2的信息,他只罗列出一种币别.
你这种方法是把所以罗列出来,假如我的币别表中有30来种,实际用到的才三种,那就有太多无用的信息显示了
create table Hou (hou_id int,m_b_l varchar(10),PAYER int)
insert hou select 1,'NY0001',1
union select 2,'NY0001',1
union select 3,'NY0002',1
union select 4,'NY0002',2
create table Finance(Finance_id int,hou_id int,freight int,currency_id int)
insert finance select 1,1,10,1
union select 2,1,20,2
union select 3,1,30,3
union select 4,2,30,1
union select 5,2,30,2
union select 6,3,30,1
union select 7,4,30,1
create table Currency (currency_id int,currency varchar(10))
insert currency select 1,'RMB'
union select 2,'USD'
union select 3,'EUR'
union select 4,'CAD'select PAYER,m_b_l,currency,freight
from (
select PAYER,m_b_l,currency_id,freight = sum(freight)
from Finance a
join Hou b on a.hou_id=b.hou_id
group by PAYER,m_b_l,currency_id
) as a
join Currency b on a.currency_id=b.currency_id
order by PAYER,m_b_l,b.currency_iddrop table Hou,Finance,currency
输出为:
PAYER m_b_l currency freight
1 NY0001 RMB 40
1 NY0001 USD 50
1 NY0001 EUR 30
1 NY0002 RMB 30
2 NY0002 RMB 30
而将输出的各个币种进行行列转换的过程建议你放到前台去实现。
try:create proc wsp
@payer int
as
declare @sql varchar(8000)
set @sql='select a.m_b_l'
select @sql=@sql+',['+currency+']=sum(case currency_id when '''+cast(currency_id as varchar)+''' then freight else 0 end)'
from Currency
set @sql=@sql+' from Finance b left join hou a on a.hou_id=b.hou_id where a.PAYER='+cast(@payer as varchar)+' group by a.m_b_l'
exec(@sql)exec wsp 1
表 Hou ,Finance,currency在数据库中已经存在,现在我的是拉某一个payer的统计信息,
假如我要payer为1的信息,他里面有三种币别,就罗列三种币别,如果我要payer为2的信息,他只罗列出一种币别.
你这种方法是把所以罗列出来,假如我的币别表中有30来种,实际用到的才三种,那就有太多无用的信息显示了
---------------------------------------------------------------------------------
你没有仔细看吧,
你的例子数据中有
4 CAD
结果集中并没有列出来呀,我的代码已经考虑了这种情况,应该符合你的要求。
另:如果你只要PAYER为某特定值的结果集,只需将代码修改一下,红色部分为增加的。
其中的数字1可以替换成你需要的值:
set @s = @s+' where a.hou_id = b.hou_id and a.hou_id = '+1+' group by payer,m_b_l order by payer,m_b_l'
create table Hou (hou_id int,m_b_l varchar(10),PAYER int)
insert hou select 1,'NY0001',1
union select 2,'NY0001',1
union select 3,'NY0002',1
union select 4,'NY0002',2
create table Finance(Finance_id int,hou_id int,freight int,currency_id int)
insert finance select 1,1,10,1
union select 2,1,20,2
union select 3,1,30,3
union select 4,2,30,1
union select 5,2,30,2
union select 6,3,30,1
union select 7,4,30,1
create table Currency (currency_id int,currency varchar(10))
insert currency select 1,'RMB'
union select 2,'USD'
union select 3,'EUR'
union select 4,'CAD' declare @s varchar(1000),@s1 varchar(1000)
set @s = ''
select @s = @s + ',sum(case when currency_id = '+rtrim(currency_id)+' then freight else 0 end) as '+currency
from currency where currency_id in(select currency_id from Finance a,hou b where a.hou_id = b.hou_id and b.payer = 1)
set @s = 'select payer,m_b_l '+@s+' from Hou a,finance b'
set @s = @s+' where a.hou_id = b.hou_id and a.payer = 1 group by payer,m_b_l order by payer,m_b_l'
exec(@s)
/*
payer m_b_l RMB USD EUR
----------- ---------- ----------- ----------- -----------
1 NY0001 40 50 30
1 NY0002 30 0 0
*/
declare @s varchar(1000),@s1 varchar(1000)
set @s = ''
select @s = @s + ',sum(case when currency_id = '+rtrim(currency_id)+' then freight else 0 end) as '+currency
from currency where currency_id in(select currency_id from Finance a,hou b where a.hou_id = b.hou_id and b.payer = 2)
set @s = 'select payer,m_b_l '+@s+' from Hou a,finance b'
set @s = @s+' where a.hou_id = b.hou_id and a.payer = 2 group by payer,m_b_l order by payer,m_b_l'
exec(@s)
/*
payer m_b_l RMB
----------- ---------- -----------
2 NY0002 30
*/