表1 A 表2 A' B C 要把表1和表2联合查询WHERE A = A'得到 A B C 这种形式, 但是,表2的B,C的列名不知道
select a.A,b.* from [表1] a inner join [表2]b on a.A=b.A'
这样的话,不是得到了A A' B C了吗,我的需求是不能有A'列
--得到第二,三列的列名 declare @s nvarchar(255)select @s=c1.name+','+c2.name FROM sys.objects O INNER JOIN sys.columns C1 ON C1.[object_id]=O.[object_id] AND O.type=N'U' and C1.column_id=2 INNER JOIN sys.columns C2 ON C1.[object_id]=C2.[object_id] AND O.type=N'U' and C2.column_id=3 where O.name=N'表2' --这里tb是表名,应该写表2exec('select 表1.A,'+@s+' from 表1 inner join 表2 on 表1.A=表2.A2')
--表2 SELECT @Cols = @Cols + ',' + Name FROM sys.columns WHERE object_id = OBJECT_ID(N'表2') AND name!='A‘'SELECT @Cols = Right(@Cols, LEN(@Cols)-1)exec('select 表1.A,'+@Cols+' from 表1 inner join 表2 on 表1.A=表2.A’')
动态生成 一般通过存储过程完成的,如果只用一sql语句估计是难以完成
A
表2
A' B C
要把表1和表2联合查询WHERE A = A'得到
A B C 这种形式,
但是,表2的B,C的列名不知道
select a.A,b.*
from [表1] a
inner join [表2]b on a.A=b.A'
declare @s nvarchar(255)select @s=c1.name+','+c2.name
FROM sys.objects O
INNER JOIN sys.columns C1
ON C1.[object_id]=O.[object_id] AND O.type=N'U' and C1.column_id=2
INNER JOIN sys.columns C2
ON C1.[object_id]=C2.[object_id] AND O.type=N'U' and C2.column_id=3
where O.name=N'表2' --这里tb是表名,应该写表2exec('select 表1.A,'+@s+' from 表1 inner join 表2 on 表1.A=表2.A2')
declare @Cols nvarchar(255)SELECT @Cols = ''
--表2
SELECT @Cols = @Cols + ',' + Name FROM sys.columns WHERE object_id = OBJECT_ID(N'表2') AND name!='A‘'SELECT @Cols = Right(@Cols, LEN(@Cols)-1)exec('select 表1.A,'+@Cols+' from 表1 inner join 表2 on 表1.A=表2.A’')