有两个表:
表A:
id data type
0001 26 AA1
0002 23 AA1
0003 56 AA1
0004 25 AA1表B:
id data type
0001 24 AA0
0002 26 AA0
0005 23 AA0
0001 26 AA2
0006 26 AA2
其中b表的type可以有多个值,如AA3、AA4、AB1等等想获得的结果是:
id AA0-data AA1-data AA2-data
0001 24 26 26
0002 26 23
0003 56
0004 25
0005 23
0006 26即:查询的结果包含了两表的所有的id,并且b表中type有几种值,查询后的列数就是(其值的个数+a表type列中值的个数)。请问这样的问题有什么办法来实现?
如果一条sql实现不了,还有什么其他的办法呢?或者程序也可。
表A:
id data type
0001 26 AA1
0002 23 AA1
0003 56 AA1
0004 25 AA1表B:
id data type
0001 24 AA0
0002 26 AA0
0005 23 AA0
0001 26 AA2
0006 26 AA2
其中b表的type可以有多个值,如AA3、AA4、AB1等等想获得的结果是:
id AA0-data AA1-data AA2-data
0001 24 26 26
0002 26 23
0003 56
0004 25
0005 23
0006 26即:查询的结果包含了两表的所有的id,并且b表中type有几种值,查询后的列数就是(其值的个数+a表type列中值的个数)。请问这样的问题有什么办法来实现?
如果一条sql实现不了,还有什么其他的办法呢?或者程序也可。
insert into #A select '0001',26,'AA1'
union all select '0002',23,'AA1'
union all select '0003',56,'AA1'
union all select '0004',25,'AA1'
create table #B(id varchar(100),data int,type varchar(100))
insert into #B select '0001',24,'AA0'
union all select '0002',26,'AA0'
union all select '0005',23,'AA0'
union all select '0001',26,'AA2'
union all select '0006',26,'AA2'
select id,
'date_AA0'=sum(case when type='AA0' then data else 0 end),
'date_AA1'=sum(case when type='AA1' then data else 0 end),
'date_AA2'=sum(case when type='AA2' then data else 0 end)
from
(
select id,data,type from #A
union all
select id,data,type from #B
) t
group by id
select id,aa0-date=date from b where type='aa0'
select id,aa2-date=date from b where type='aa2'
这3张表连接查询就行了
declare @sql varchar(1000)
select @sql=''
select @sql=@sql+'select id,'
select @sql=@sql+'date_'+type +'=sum(case when type='''+type+''' then data else 0 end),'
from
(
select type from #A
union all
select type from #B
) t
group by typeselect @sql=left(@sql,len(@sql)-1)
select @sql=@sql+' from
(
select id,data,type from #A
union all
select id,data,type from #B
) t
group by id'execute(@sql)满足列值根据2表type变动
所以要寫個動態的才行
insert into a values('0001', '26', 'AA1')
insert into a values('0002', '23', 'AA1')
insert into a values('0003', '56', 'AA1')
insert into a values('0004', '25', 'AA1')
create table b(id varchar(10),data varchar(10),type varchar(10))
insert into b values('0001', '24', 'AA0')
insert into b values('0002', '26', 'AA0')
insert into b values('0005', '23', 'AA0')
insert into b values('0001', '26', 'AA2')
insert into b values('0006', '26', 'AA2')
godeclare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ' , min(case type when ''' + type + ''' then data end) [' + type + ']'
from (select distinct type from (select * from a union all select * from b) m) as t
set @sql = @sql + ' from (select * from a union all select * from b) m group by id'
exec(@sql) drop table a,b
/*
id AA0 AA1 AA2
---------- ---------- ---------- ----------
0001 24 26 26
0002 26 23 NULL
0003 NULL 56 NULL
0004 NULL 25 NULL
0005 23 NULL NULL
0006 NULL NULL 26
*/
如果不固定,就要用动态的方法。相对要麻烦一点
动物大哥
我写了动态的...先写一个不动态的占位置... hoho