27,579
社区成员
发帖
与我相关
我的任务
分享■■ 这个“不存在”的存储过程如何写？
1、字符串：M1234,M2345,M7986,M3456（编号个数不确定，可能多，可能少）
2、TB表NUM字段：
-----------
M1234
M1235
M3454
M3456
....
3、要求执行存储过程：Query_inexistence 'M1234,M2345,M7986,M3456'得到结果集
-----
M2345
M7986
解释：也就是说，看给定的字符串中所含编号哪些不在TB表中，因为TB表中数据量很大，所以这个存储过程要求执行效率较高才行。请高手帮忙！
...全文
请发表友善的回复…
发表回复
五维思考 2008-12-24
- 打赏
- 举报
本人最终采用了 5 楼 csdyyr 的方法
爱新觉罗.毓华 的方法也不错，感谢热心帮助
近身剪 的方法得不到正确的结果，但改一下就行了，在此表示感谢
爱新觉罗.毓华 的方法也不错，感谢热心帮助
近身剪 的方法得不到正确的结果，但改一下就行了，在此表示感谢
claro 2008-12-24
- 打赏
- 举报
帮顶啦
等不到来世 2008-12-24
- 打赏
- 举报
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([NUM] varchar(5))
insert [tb]
select 'M1234' union all
select 'M1235' union all
select 'M3454' union all
select 'M3456'
go
--select * from [tb]
if object_id('p','p') is not null
drop proc p
go
create proc p
@s nvarchar(1000)
as
select top 100 id=identity(int,1,1) into # from syscolumns,sysobjects
select num=substring(@s,id,charindex(',',@s+',',id)-id)
into #1
from #
where charindex(',',','+@s,id)=id
delete #1
from tb
where #1.num=tb.NUM
select num from #1
go
exec p 'M1234,M2345,M7986,M3456'
/*
num
-------
M2345
M7986
*/
水族杰纶 2008-12-24
- 打赏
- 举报
CREATE PROC PROC_TEST
@STR VARCHAR(1000)
AS
SELECT * FROM TB WHERE PATINDEX(','+NUM+',',','+@STR+',')>0五维思考 2008-12-24
- 打赏
- 举报
哇，这么多答案，先谢谢各位兄弟，我现在试试看
viva369 2008-12-24
- 打赏
- 举报
楼主试下哪个比较快，试之前清下缓存
dbcc dropcleanbuffers
dbcc dropcleanbuffers
viva369 2008-12-24
- 打赏
- 举报
试试这个
create procedure Query_inexistence
(
@str nvarchar(max)
)
as
begin
declare @sql varchar(max)
set @sql =
'
select *
from
(
select null NUM union all select ' + replace(@str,',',' union all select ')
+')tbl
where not exists(select 1 from TB where tb.num = tbl.num) and tbl.num is not null
'
exec (@sql)
end
jinjazz 2008-12-24
- 打赏
- 举报
--建立测试环境
set nocount on
create table test(num varchar(20))
insert into test select 'M1234'
insert into test select 'M1235'
insert into test select 'M3454'
insert into test select 'M3456'
go
--测试
select * from test where charindex(','+num+',',
',M1234,M2345,M7986,M3456,')>0
--删除测试环境
drop table test
set nocount off
/*
num
--------------------
M1234
M3456
*/jinjazz 2008-12-24
- 打赏
- 举报
select * from test where charindex(','+num+',',
',M1234,M2345,M7986,M3456,')>0
csdyyr 2008-12-24
- 打赏
- 举报
CREATE TABLE TB(NUM VARCHAR(5))
INSERT TB
SELECT 'M1234' UNION ALL
SELECT 'M1235' UNION ALL
SELECT 'M3454' UNION ALL
SELECT 'M3456'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT COL='''+REPLACE('M1234,M2345,M7986,M3456', ',', ''' UNION ALL SELECT ''')+''''
SET @SQL='SELECT COL FROM ('+@SQL+') T LEFT JOIN TB ON COL=NUM WHERE NUM IS NULL'
EXEC(@SQL)
DROP TABLE TB
/*
COL
-----
M2345
M7986
*/
dawugui 2008-12-24
- 打赏
- 举报
create table TB(NUM varchar(10))
insert into tb values('M1234')
insert into tb values('M1235')
insert into tb values('M3454')
insert into tb values('M3456')
go
/*
名称：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 @s varchar(100)
set @s = 'M1234,M2345,M7986,M3456'
select m.* from (select * from dbo.fn_split(@s,',')) m where a not in (select * from tb)
drop table tb
drop function dbo.fn_split
/*
a
------
M2345
M7986
（所影响的行数为 2 行）
*/
liangCK 2008-12-24
- 打赏
- 举报
将
'M1234,M2345,M7986,M3456'
变为
M1234
M2345
M7986
...
...
的形式.然后再JOIN
'M1234,M2345,M7986,M3456'
变为
M1234
M2345
M7986
...
...
的形式.然后再JOIN
dawugui 2008-12-24
- 打赏
- 举报
/*
标题：分解字符串并查询相关数据
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：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 行）
*/
dawugui 2008-12-24
- 打赏
- 举报
先分解字符串,然后再比较.
