select P.name,B.* from (
select * from (
select name,idcode = case D1 when 1 then 'D1' else '0' end from A
union all
select name,idcode = case D2 when 1 then 'D2' else '0' end from A
union all
select name,idcode = case D3 when 1 then 'D3' else '0' end from A
union all
select name,idcode = case D4 when 1 then 'D4' else '0' end from A
union all
select name,idcode = case D5 when 1 then 'D5' else '0' end from A
) G where idcode <> '0'
)P,B where P.idcode = B.idcode
select * from (
select name,idcode = case D1 when 1 then 'D1' else '0' end from A
union all
select name,idcode = case D2 when 1 then 'D2' else '0' end from A
union all
select name,idcode = case D3 when 1 then 'D3' else '0' end from A
union all
select name,idcode = case D4 when 1 then 'D4' else '0' end from A
union all
select name,idcode = case D5 when 1 then 'D5' else '0' end from A
) G where idcode <> '0'
)P,B where P.idcode = B.idcode
insert A select 'aa', 1, 0, 0, 1, 1
insert A select 'bb', 0, 1, 1, 0, 0
go
create table B( id int, idcode nvarchar(2) , xm nvarchar(5))
insert B select 1, 'D1', 'ssss'
insert B select 2, 'D2', 'aaaa'
insert B select 3, 'D3', 'ffff'
insert B select 4, 'D4', 'gggg'
insert B select 5, 'D5', 'hhhh'
godeclare @s nvarchar(4000)
select
@s=isnull(@s+' union all ','')+'select [Col]='+quotename(Name,'''')+' from A where Name=''aa'' and '+quotename(Name)+'=1'
from
syscolumns a
where
a.ID=object_id('A') and a.colid>1exec ('select * from B where idcode in(select col from ('+@s+')tmp)')id idcode xm
----------- ------ -----
1 D1 ssss
4 D4 gggg
5 D5 hhhh
declare @s nvarchar(4000)
select
@s=isnull(@s+' union all ','')+'select [Col]='+quotename(Name,'''')+' from A where Name=''aa'' and '+quotename(Name)+'=1'
from
syscolumns a
where
a.ID=object_id('A') and a.colid>1exec ('select b.* from B join ('+@s+')tmp on b.idcode=tmp.col')--改连接方式
将 nvarchar 值 'baizf ' 转换为数据类型为 int 的列时发生语法错误。
好像和字符串长短有关没有理解。
select
col= ' select [Col]= ' + quotename (Name, '''' ) + ' from A where Name= '' aa '' and ' + quotename (Name) + ' =1 '
,ID=identity(int,1,1),row=1
into #from
syscolumns a
where
a.ID = object_id ( ' A ' ) and a.colid > 1 select @i=max(len(col)) from #set @i=3800/@i--得到需要的变量数量select @s2='',@s3=''
update #
set row=ID/@i
----------------------select
@s1=case when row=0 then isnull(@s+' union all ','')+col else @s1 end,
@s2=case when row=1 then @s+' union all '+col else @s2 end,
@s3=case when row=2 then @s+' union all '+col else @s2 end
from
#
exec(@s1+@s2+@s3)
insert A select 'aa', 1, 0, 0, 1, 1
insert A select 'bb', 0, 1, 1, 0, 0
go
create table B( id int, idcode nvarchar(2) , xm nvarchar(5))
insert B select 1, 'D1', 'ssss'
insert B select 2, 'D2', 'aaaa'
insert B select 3, 'D3', 'ffff'
insert B select 4, 'D4', 'gggg'
insert B select 5, 'D5', 'hhhh'
godeclare @s1 nvarchar (4000),@i int,@s2 nvarchar(4000)
declare @s3 nvarchar(4000)
select
col= ' select [Col]= ' + quotename (Name, '''' ) + ' from A where Name= ''aa'' and ' + quotename (Name) + ' =1 '
,ID=identity(int,1,1),row=1
into #from
syscolumns a
where
a.ID = object_id ( 'A' ) and a.colid > 1 select @i=max(len(col)) from #
set @i=3800/@i--得到需要的变量数量select @s2='',@s3=''update # set row=ID/@iselect
@s1=case when row=0 then isnull(@s1+' union all ','')+col else @s1 end,
@s2=case when row=1 then @s2+' union all '+col else @s2 end,
@s3=case when row=2 then @s3+' union all '+col else @s3 end
from #
exec('select * from b where idcode in('+@s1+@s2+@s3+')')
drop table a,b
drop table #/*
id idcode xm
----------- ------ -----
1 D1 ssss
4 D4 gggg
5 D5 hhhh*/
exec ('select * from B where idcode in(select col from ('+@s+')tmp)')
--用以下方式,楼主看看能不能理解use test
go
create table B( id int, idcode nvarchar(10) , xm nvarchar(5))
insert B select 1, 'D1', 'ssss'
insert B select 2, 'D2', 'aaaa'
insert B select 3, 'D3', 'ffff'
insert B select 4, 'D4', 'gggg'
insert B select 5, 'D5', 'hhhh'
insert B select 6, 'D365', 'xxxx' --增加两条记录看效果
insert B select 7, 'D366', 'yyyy'
go
------创建测试:366列
declare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000), @i int
select @s='',@s2='',@s3='',@s4='',@i=1while len(@s)!>3900
select @s=+@s+',[D'+rtrim(@i)+'] int',
@s2=@s2+',[D'+rtrim(@i)+']',
@s3=@s3+','+rtrim(@i),
@s4=@s4+','+rtrim(@i+5),
@i=@i+1
exec('create table A(Name nvarchar(10)'+@s+')')exec('insert A(Name'+@s2+')values(''aa'''+@S3+')')exec('insert A(Name'+@s2+')values(''BB'''+@S4+')')
--drop table A------
select
col= ' select [Col]= ' + quotename (Name, '''' ) + ' from A where Name= ''aa'' and ' + quotename (Name) + ' =1 '
,ID=identity(int,1,1),row=1
into #
from
syscolumns
where
ID = object_id ( 'A' ) and colid > 1 order by colid asc
create table #1(Col nvarchar(10))--创建存储的有效值declare test cursor for
select Col from #
declare @SQL nvarchar(4000)
open test
fetch next from test into @SQL
while @@fetch_status=0
begin
exec('insert #1 '+@SQL)
fetch next from test into @SQL
end
close test
deallocate test--
select * from b where idcode in(select col from #1)
id idcode xm
----------- ---------- -----
1 D1 ssss(所影响的行数为 1 行)