if OBJECT_ID('test') is not null drop table test; go create table test ( id int null, zd varchar(50) null )insert into test values('1','a1'), ('2','a2'), ('3','a3'), ('4','a4'), ('5','a5'), ('6','a6') goif OBJECT_ID('f_splits') is not null drop function f_splits; go create function f_splits(@ids varchar(2000)) returns @t_split table(col varchar(20)) as begin --循环找到字符串中第一个','的索引 while(charindex(',',@ids)<>0) begin --将第一个','之前的字符单元插入临时表中 insert @t_split(col) values(substring(@ids,1,charindex(',',@ids)-1)) --将第一个','后面的字符串重新赋给@ids set @ids=stuff(@ids,1,charindex(',',@ids),'') end --将最后一个字符单元插入表中(已经没有',') insert @t_split(col) values(@ids) return end goif OBJECT_ID('usp_getinfo') is not null drop proc usp_getinfo; go create proc usp_getinfo @Condition varchar(50) asselect * from [test] where zd in (select * from f_splits(@Condition)) goexec usp_getinfo @Condition='a4,a1,a6,a2,a20,a2'坐等大神回复...
感谢朋友的热心参与use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( id int, zd char(3) ) go --插入测试数据 insert into tb select 1,'a1' union all select 2,'a2' union all select 3,'a3' union all select 4,'a4' union all select 5,'a5' union all select 6,'a6' go --代码实现 if object_id('test.dbo.o_pro') is not null drop proc o_pro go create proc o_pro @str varchar(50) as begin ;with cte as ( select number, item = substring(@str, number, charindex(',', @str + ',', number) - number) from master..spt_values where number < len(@str) and type='p' and substring(',' + @str, number, 1) = ',' ) select zd, id from cte t1, tb t2 where not exists (select 1 from cte where item = t1.item and number < t1.number) and t1.item = t2.zd order by number end go exec o_pro 'a4,a1,a6,a2,a20,a2'
drop table test;
go
create table test
(
id int null,
zd varchar(50) null
)insert into test values('1','a1'),
('2','a2'),
('3','a3'),
('4','a4'),
('5','a5'),
('6','a6')
goif OBJECT_ID('f_splits') is not null
drop function f_splits;
go
create function f_splits(@ids varchar(2000))
returns @t_split table(col varchar(20))
as
begin
--循环找到字符串中第一个','的索引
while(charindex(',',@ids)<>0)
begin
--将第一个','之前的字符单元插入临时表中
insert @t_split(col) values(substring(@ids,1,charindex(',',@ids)-1))
--将第一个','后面的字符串重新赋给@ids
set @ids=stuff(@ids,1,charindex(',',@ids),'')
end
--将最后一个字符单元插入表中(已经没有',')
insert @t_split(col) values(@ids)
return
end
goif OBJECT_ID('usp_getinfo') is not null
drop proc usp_getinfo;
go
create proc usp_getinfo @Condition varchar(50)
asselect * from [test] where zd in (select * from f_splits(@Condition))
goexec usp_getinfo @Condition='a4,a1,a6,a2,a20,a2'坐等大神回复...
usp_getinfo @Condition='a4,a1,a6,a2,a20,a2'zd ida4 4
a1 1
a6 6
a2 2
与我预想的还有点出入 我希望排序的方式是按条件中的排序(上面的效果)
感谢朋友的热心参与use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
zd char(3)
)
go
--插入测试数据
insert into tb select 1,'a1'
union all select 2,'a2'
union all select 3,'a3'
union all select 4,'a4'
union all select 5,'a5'
union all select 6,'a6'
go
--代码实现
if object_id('test.dbo.o_pro') is not null drop proc o_pro
go
create proc o_pro
@str varchar(50)
as
begin
;with cte as (
select number, item = substring(@str, number, charindex(',', @str + ',', number) - number)
from master..spt_values
where number < len(@str) and type='p' and substring(',' + @str, number, 1) = ','
)
select zd, id from cte t1, tb t2
where not exists (select 1 from cte where item = t1.item and number < t1.number)
and t1.item = t2.zd
order by number
end
go
exec o_pro 'a4,a1,a6,a2,a20,a2'
/*测试结果
zd id
---------------------
a4 4
a1 1
a6 6
a2 2
(4行受影响)
*/