求sql高手帮忙？
表1 V
DW_ID KM_ID BM_ID VAL
101 201 301 123.00
102 202 302 500.00
103 203 303 600.00
表2 DY (对应表)
BASE_id DY_FIELD
1 DW_ID
2 KM_ID
表3 BASE
id name
1 sx01
2 sx02
表4 SX01
ID DY_ID
8001 101
8002 102
表5 SX02
ID DY_ID
7002 202
7003 203
完全对应上的显示:
第一个SQL结果为:
SX01_ID SX02_ID BM_ID VAL
8002 7002 302 500.00
否则没有对应上的显示:
第二个SQL结果为:
DW_ID KM_ID BM_ID VAL
101 201 301 123.00
103 203 303 600.00
[解决办法]
create table #V(DW_ID int, KM_ID int, BM_ID int, VAL decimal(10,2))
insert #V select 101, 201, 301, 123.00
union all select 102, 202, 302, 500.00
union all select 103, 203, 303, 600.00
Create table #DY(BASE_id int identity(1,1), DY_FIELD varchar(10))
insert #DY select 'DW_ID '
union all select 'KM_ID '
Create table #BASE(id int identity(1,1), name varchar(10))
insert #BASE select 'sx01 '
union all select 'sx02 '
Create table #SX01([id] bigint, DY_ID int)
insert #SX01 select 8001, 101
union all select 8002, 102
Create table #SX02([id] bigint, DY_ID int)
insert #SX02 select 7002, 202
union all select 7003, 203
declare @s varchar(8000)
set @s = 'select '
select @s = @s + 'convert(varchar,DW_ID) [ ' + isnull((select name + '_id ' from #DY dy join #BASE b on dy.BASE_id=b.id where dy.DY_FIELD= 'DW_ID '), 'DW_ID ')
+ '], ' + 'convert(varchar,KM_ID) [ ' + isnull((select name + '_id ' from #DY dy join #BASE b on dy.BASE_id=b.id where dy.DY_FIELD= 'KM_ID '), 'KM_ID ')
+ '], ' + 'convert(varchar,BM_ID) [ ' + isnull((select name + '_id ' from #DY dy join #BASE b on dy.BASE_id=b.id where dy.DY_FIELD= 'BM_ID '), 'BM_ID ')
+ '], ' + 'convert(varchar,VAL) [VAL] ' + 'from #V v where DW_ID in (select DY_ID from #SX01) and KM_ID in (select DY_ID from #SX02) '
exec (@s)
select * from #V v where DW_ID not in (select DY_ID from #SX01) or KM_ID not in (select DY_ID from #SX02)
drop table #SX02
drop table #SX01
drop table #BASE
drop table #DY
drop table #V
/*
sx01_id sx02_id BM_ID VAL
---------------------------------------------
102 202 302 500.00
DW_ID KM_ID BM_ID VAL
----------- ----------- ----------- ------------
101 201 301 123.00
103 203 303 600.00
*/
