求一解决方案SQL

[zhnzzy]

现在我向存储过程传递参数
比如@id int
类型为 2,3,4
现在表里面满足数据的记录的可能大于等于@id里面的集合，比如满足条件的行数的ID，可能是2，3，4，5

现在要找出ID为5的这一条将之删掉，怎么实现的。
比如

slect * from tb where ....将这里得到的结果集，里面所有ID集合和@id拆分出来的ID进行比较，删除◎ID里面没有的
不知道我的描述大家是否看的懂哦

[dawugui]

/*
标题：分解字符串并查询相关数据
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 
时间：2008-03-18
地点：广东深圳
说明：通过使用函数等方法分解字符串查询相关数据。

问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1  1,2,3,4,5,6,7,8,9,10,11,12
2  2,3 
3  3,7,8,9 
4  2,6 
5  4,5
6  6,7 
*/
-----------------------------
create table tb (ID int , TypeID varchar(30)) 
insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12') 
insert into tb values(2 , '2,3') 
insert into tb values(3 , '3,7,8,9') 
insert into tb values(4 , '2,6') 
insert into tb values(5 , '4,5')
insert into tb values(6 , '6,7')
go
-----------------------------
--如果仅仅是一个,如@str = '1'.
declare @str as varchar(30)
set @str = '1'
select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0
select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%'
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
（所影响的行数为 1 行）
*/

-----------------------------
--如果包含两个,如@str = '1,2'.
declare @str as varchar(30)
set @str = '1,2'
select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or 
  charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0
select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or 
  ',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
4           2,6
（所影响的行数为 3 行）
*/

-------------------------------------------
--如果包含三个或四个，用PARSENAME函数来处理.
declare @str as varchar(30)
set @str = '1,2,3,4'
select * from tb where 
  charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0 
select * from tb where 
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/

---------------------------------------
--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as 
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @inputstr <> '\'
  insert @temp values(@inputstr)
  return 
end
go

--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'

select distinct m.* from tb m,
(select * from dbo.fn_split(@str,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0

drop table tb
drop function dbo.fn_split 

/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/

------------------------------------------
--使用动态SQL的语句。
declare @str varchar(200)
declare @sql as varchar(1000)
set @str = '1,2,3,4,5'
set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')
set @sql = @sql + ''''
set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)
/*
ID          TypeID                         
----------- ------------------------------ 
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/

[zhnzzy]

比如

 CREATE PROCEDURE Deldetail
(
 @idlist varchar(100)
)
 AS
SET XACT_ABORT ON
BEGIN TRAN
declare @EX_Code varchar(100)
declare @itemtype varchar(100)
declare @i int 
set @i=charindex(',',@idlist,1) 

select @EX_Code= EX_Code, @itemtype=itemtype from ExamineDetail where [id]=cast(left(@idlist,@i-1) as int)
select  *  from ExamineDetail where  EX_Code=@EX_Code and itemtype=@itemtype
 
COMMIT TRAN
GO

下面我要比较select * from ExamineDetail where EX_Code=@EX_Code and itemtype=@itemtype的结果集，和◎idlist 对应的结果集，将@idlist 里面没有的记录删掉

[水族杰纶]

--分解後比較
create function dbo.fn_split 
( 
@inputstr varchar(8000), 
@seprator varchar(10) 
) 
returns @temp table (a varchar(200)) 
as 
begin 
declare @i int 
set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr) 
while @i >= 1 
begin 
insert @temp values(left(@inputstr, @i - 1)) 
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 
if @inputstr <> '\' 
insert @temp values(@inputstr) 
return 
end 
go 
--调用 
declare @s varchar(1000) 
set @s='1,2,3,4,5,6,7,8,55' 
select * from dbo.fn_split(@s,',') 
drop function dbo.fn_split 

[zhnzzy]

呵呵，大乌龟好快呀，我先看看，我是想比较下，这种情况到底是在数据库里面实现比较方便还是在应用程序里面比较方便呢

[hery2002]

字符拆分～

[dawugui]

应用程序里面比较方便

[水族杰纶]

[Quote=引用 2 楼 zhnzzy 的回复:]
比如

SQL code
CREATE PROCEDURE Deldetail
(
@idlist varchar(100)
)
AS
SET XACT_ABORT ON
BEGIN TRAN
declare @EX_Code varchar(100)
declare @itemtype varchar(100)
declare @i int
set @i=charindex(',',@idlist,1)

select @EX_Code= EX_Code, @itemtype=itemtype from ExamineDetail where [id]=cast(left(@idlist,@i-1) as int)
select * from ExamineDetail where EX_Code=@EX_Code and ite…
[/Quote]

--TRY
CREATE PROCEDURE Deldetail
(
 @idlist varchar(100)
)
 AS
SET XACT_ABORT ON
BEGIN TRAN
delete   ExamineDetail where charindex(','+ltrim(id)+',',','+@idlist+',')=0 
COMMIT TRAN
GO

[zhnzzy]

[Quote=引用 7 楼 wufeng4552 的回复:]
引用 2 楼 zhnzzy 的回复:
比如

SQL code
CREATE PROCEDURE Deldetail
(
@idlist varchar(100)
)
AS
SET XACT_ABORT ON
BEGIN TRAN
declare @EX_Code varchar(100)
declare @itemtype varchar(100)
declare @i int
set @i=charindex(',',@idlist,1)

select @EX_Code= EX_Code, @itemtype=itemtype from ExamineDetail where [id]=cast(left(@idlist,@i-1) as int)
select * from ExamineDeta…
[/Quote]

不错，用这样可以
delete from ExamineDetail where [id] in(select [id] from ExamineDetail where EX_Code=@EX_Code and itemtype=@itemtype) and charindex(cast([id] as varchar(10)),@idlist)=0

[claro]

结吧