34,575
社区成员
发帖
与我相关
我的任务
分享
if object_id('t1') is not null
drop table t1
go
create table t1(ENFIELD char(1), CHNAME varchar(10))
insert into t1
select 'A', '序号1' union all
select 'B', '序号2' union all
select 'C', '序号3' union all
select 'D', '序号4' union all
select 'E', '序号5'
if object_id('t2') is not null
drop table t2
go
create table t2(A varchar(10), B varchar(10), C varchar(10), D varchar(10), E varchar(10))
insert into t2
select '值1', '值2', '值3', '值4', '值5'
DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql + ' UNION ALL ','') +
'SELECT ENFIELD=''' + name + ''',[VALUES]=(SELECT TOP 1 ' + name + ' FROM t2)'
FROM syscolumns
WHERE id=object_id('t2')
print @sql
EXEC('SELECT a.ENFIELD ,a.CHNAME,b.[VALUES] FROM t1 a'+
' INNER JOIN (' + @sql + ') b ON a.ENFIELD=b.ENFIELD'+
' where (a.ENFIELD=''A'' and b.[VALUES]=''值'') or (a.ENFIELD=''C'' and b.[VALUES]=''值'')')
if object_id('t1') is not null
drop table t1
go
create table t1(ENFIELD char(1), CHNAME varchar(10))
insert into t1
select 'A', '序号1' union all
select 'B', '序号2' union all
select 'C', '序号3' union all
select 'D', '序号4' union all
select 'E', '序号5'
if object_id('t2') is not null
drop table t2
go
create table t2(A varchar(10), B varchar(10), C varchar(10), D varchar(10), E varchar(10))
insert into t2
select '值1', '值2', '值3', '值4', '值5'
declare @s varchar(8000)
set @s=';with t as (select *, (select case ENFIELD'
select @s=@s + ' when '''+ENFIELD+''' then '+ENFIELD from t1
set @s=@s+' end from t2) [values] from t1)'+
' select * from t where (ENFIELD=''A'' and [VALUES]=''值1'') or (ENFIELD=''C'' and [VALUES]=''值3'')'
exec(@s)
if object_id('表1')is not null
drop table 表1
if object_id('表2') is not null
drop table 表2
if object_id('c') is not null
drop table c
go
create table 表1
( ENFIELD nvarchar(2),
CHNAME nvarchar(6)
)
insert into 表1 select 'A','序号1'
union all select 'B','序号2'
union all select 'C','序号3'
union all select 'D','序号4'
union all select 'E','序号5'
go
create table 表2
( A nvarchar(4),
B nvarchar(4),
C nvarchar(4),
D nvarchar(4),
E nvarchar(4)
)
insert into 表2 select '值1' ,'值2', '值3', '值4', '值5'
go
select A AS VALUSE,ENFIELD INTO c FROM
(select A ,'A' AS ENFIELD from 表2
union all
select B ,'B' from 表2
union all
select C ,'C' from 表2
union all
select D,'D' from 表2
union all
select E ,'E'from 表2) b
select a.ENFIELD,a.CHNAME,c.VALUSE FROM 表1 a inner join c on a.ENFIELD=c.ENFIELD
------
A 序号1 值1
B 序号2 值2
C 序号3 值3
D 序号4 值4
E 序号5 值5