/*
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: T1
If object_id('T1') is not null
Drop table T1
Go
Create table T1(ID int,UID int,NAME varchar(1))
Go
Insert into T1
select 1,1,'a' union all
select 2,2,'b' union all
select 3,3,'c'
Go
-- Test Data: T2
If object_id('T2') is not null
Drop table T2
Go
Create table T2(ID int,UID int,TA varchar(1),TB varchar(2))
Go
Insert into T2
select 1,1,'A','aa' union all
select 2,1,'B','bb' union all
select 3,1,'C','cc' union all
select 4,2,'A','aa' union all
select 5,2,'B','bb' union all
select 6,3,'A','aa'
Go
--Start
select a.id,a.uid,
ta1 = max(case when b.ta = 'a' then b.ta else ''end),
tb1 = max(case when b.tb = 'aa' then b.tb else '' end),
ta2 = max(case when b.ta = 'b' then b.ta else ''end),
tb2 = max(case when b.tb = 'bb' then b.tb else '' end),
ta3 = max(case when b.ta = 'c' then b.ta else '' end),
tb3 = max(case when b.tb = 'cc' then b.tb else '' end)
from t1 a ,t2 b
where a.uid = b.uid
group by a.id,a.uid
--Result:
/*id uid ta1 tb1 ta2 tb2 ta3 tb3
----------- ----------- ---- ---- ---- ---- ---- ----
1 1 A aa B bb C cc
2 2 A aa B bb
3 3 A aa (所影响的行数为 3 行)
*/
--End
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: T1
If object_id('T1') is not null
Drop table T1
Go
Create table T1(ID int,UID int,NAME varchar(1))
Go
Insert into T1
select 1,1,'a' union all
select 2,2,'b' union all
select 3,3,'c'
Go
-- Test Data: T2
If object_id('T2') is not null
Drop table T2
Go
Create table T2(ID int,UID int,TA varchar(1),TB varchar(2))
Go
Insert into T2
select 1,1,'A','aa' union all
select 2,1,'B','bb' union all
select 3,1,'C','cc' union all
select 4,2,'A','aa' union all
select 5,2,'B','bb' union all
select 6,3,'A','aa'
Go
--Start
select a.id,a.uid,
ta1 = max(case when b.ta = 'a' then b.ta else ''end),
tb1 = max(case when b.tb = 'aa' then b.tb else '' end),
ta2 = max(case when b.ta = 'b' then b.ta else ''end),
tb2 = max(case when b.tb = 'bb' then b.tb else '' end),
ta3 = max(case when b.ta = 'c' then b.ta else '' end),
tb3 = max(case when b.tb = 'cc' then b.tb else '' end)
from t1 a ,t2 b
where a.uid = b.uid
group by a.id,a.uid
--Result:
/*id uid ta1 tb1 ta2 tb2 ta3 tb3
----------- ----------- ---- ---- ---- ---- ---- ----
1 1 A aa B bb C cc
2 2 A aa B bb
3 3 A aa (所影响的行数为 3 行)
*/
--End
通用的做法????
set @sql='select a.id,a.uid'
select @sql=@sql+',TA'+ltrim(px)+'=max(case when px='+ltrim(px)+' then TA else ''0'' end)'
+',TB'+ltrim(px)+'=max(case when px='+ltrim(px)+' then TB else ''0'' end)'
from (select distinct px=(select count(1) from #tb2 where uid=a.uid and id<=a.id) from #tb2 a)b
set @sql=@sql+' from #tb1 a,(select px=(select count(1) from #tb2 where uid=a.uid and id<=a.id),* from #tb2 a)b
where a.uid=b.uid group by a.id,a.uid'
exec(@sql)
Tabel1:
ID UID NAME
1 1 客户1
2 2 客户2
3 3 客户3
...... Tabel2:
ID UID TA TB
1 1 卡车费 10.00
2 1 服务费 12.50
3 1 人工费 89.50
4 2 卡车费 10.00
5 2 服务费 125.0
6 3 卡车费 99.00
...... 要得到的结果是:
ID UID 卡车费 服务费 人工费
1 1 10.00 12.50 89.5
2 2 10.00 125.0 0.0
3 3 99.00 0.0 0.0
......