设有表T
Col1 Col2
abc A
def A
sss B
cbd A
sss D
现在类似这样的SQL:
select * from T where Col1 in ('abc','def','abc','sss'),希望能得到这样的结果集:
abc A
def A
abc A
sss D
就是根据in后面的序列,如果序列中有重复的值的话,需要产生相应条数的复制记录,请问这个SQL该怎么写?
Col1 Col2
abc A
def A
sss B
cbd A
sss D
现在类似这样的SQL:
select * from T where Col1 in ('abc','def','abc','sss'),希望能得到这样的结果集:
abc A
def A
abc A
sss D
就是根据in后面的序列,如果序列中有重复的值的话,需要产生相应条数的复制记录,请问这个SQL该怎么写?
create table tb(col1 varchar(10),col2 varchar(10))
insert into tb select 'abc','A'
insert into tb select 'def','A'
insert into tb select 'sss','B'
insert into tb select 'cbd','A'
insert into tb select 'sss','D'drop table tbdeclare @s varchar(8000),@sql varchar(8000)
set @s='abc,def,abc,sss'
set @s='select top 1 * from tb where col1='''+replace(@s,',',''' union all select top 1 * from tb where col1=''')+''''
exec( @s)col1 col2
abc A
def A
abc A
sss B