sql查询如何保留重复项
表:create table test(id int not null,name nvarchar(100) null)
insert into test(1,'张三');
insert into test(2,'李四');
insert into test(3,'王五');
select id,name from test where id in(1,1,2,3);
现在我需要的查询结果应该是这样的
1,张三
1,张三
2,李四
1,王五
3,王五
有什么解决办法嘛?
用union all的话最多只能有256个select

[解决办法]

SQL code

create table #test(  id int not null,  name nvarchar(100) null)insert into #test values(1,'张三')insert into #test values(2,'李四')insert into #test values(3,'王五')declare @a nvarchar(50)set @a='1,1,2,3'select a.id,a.name from #test  aleft join f_split_dh(@a) b on a.id=b.adrop table #test--标值函数f_split_dhCreate function [dbo].[f_split_dh](@SourceSql varchar(8000))returns @temp table(a varchar(100))as begin    declare @i int    set @SourceSql=rtrim(ltrim(@SourceSql))    set @i=charindex(',',@SourceSql)    while @i>=1    begin        insert @temp values(left(@SourceSql,@i-1))        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)        set @i=charindex(',',@SourceSql)    end    if @SourceSql<>','       insert @temp values(@SourceSql)    return endGO--1    张三--1    张三--2    李四--3    王五
[解决办法]
SQL code
create table t1(    id int,    name varchar(10))insert into t1 values (1,'张三')insert into t1 values (2,'李四')insert into t1 values (3,'王五')select * from t1declare @str varchar(4000)='1,1,2,3'create table #tt(    id int)set @str=REPLACE(@str,',',' union all select ')set @str=' insert into #tt select '+@strprint @strexec (@str)select t1.name from #tt inner join t1 on #tt.id=t1.iddrop table #tt--------------------name张三张三李四王五