22,209
社区成员
发帖
与我相关
我的任务
分享
select m.* from table1 m , table2 n where m.a = 1 and n.a = 100 and (m.b = n.b or m.c = n.c and m.d = n.d)
select m.* from table1 m , table2 n where m.a = 2 and n.a = 101 and (m.b = n.b or m.c = n.c and m.d = n.d)
select m.b from table1 m , table2 n where m.a = 1 and n.a = 100 and m.b = n.b
union all
select m.c from table1 m , table2 n where m.a = 1 and n.a = 100 and m.c = n.c
union all
select m.d from table1 m , table2 n where m.a = 1 and n.a = 100 and m.d = n.d
select m.b from table1 m , table2 n where m.a = 2 and n.a = 101 and m.b = n.b
union all
select m.c from table1 m , table2 n where m.a = 2 and n.a = 101 and m.c = n.c
union all
select m.d from table1 m , table2 n where m.a = 2 and n.a = 101 and m.d = n.d
要是需要竖着的结果的话,可以将上面的结果存到一个临时表里面,再行列转换一下,
行列转换的方法csdn上有的时,需要的话,楼主可以自己找找看
使用动态语句
if object_id('ta') is not null drop table ta
create table ta(a int ,b char(10),c char (10),d char(10))
insert ta values(1,'天津','上海','北京')
insert ta values(2,'湖南','广东','香港')
if object_id('tb') is not null drop table tb
create table tb(a int,b char)
insert tb values(100,'d')
insert tb values(100,'b')
insert tb values(101,'d')
insert tb values(101,'b')
insert tb values(101,'c')
declare @a int,@b int,@sql nvarchar(max)
select @a = 2,@b = 101
select @sql = tt.s from(
select a,s = stuff((select ','+cast(b as nvarchar(256)) from tb where a = t.a for xml path('')),1,1,'') from tb t group by a
) tt
where tt.a = @b
set @sql = @sql +' from ta where a ='+cast(@a as nvarchar(10))
select @sql = 'select '+ @sql
exec(@sql)
d b c
---------- ---------- ----------
香港 湖南 广东
(1 row(s) affected)
create table #t2( a int ,b char)
insert into #t2
select 100,'d' union all
select 100,'b'union all
select 101,'d' union all
select 101,'b' union all
select 101,'c'
declare @sql varchar(100)
declare @t varchar(100)
select @t=''
select @t=@t+','+b from #t2 where a=100 --a=100
select @t=substring(@t,2,len(@t)-1)
select @sql=N'select '+@t +' from #t where a=1' --a=1
PRINT @sql
exec(@sql)