日期:2014-05-17 浏览次数:20573 次
写一个笨方法:
----------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-10-08 16:36:59
-- Verstion:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
-- Feb 10 2012 19:39:15
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[IndexArr] varchar(19))
insert [tb]
select 1,'1,2,3,4,5' union all
select 2,'55,6,99,87,1000' union all
select 3,'7,567567,567,43,123' union ALL
SELECT 4,'2,34,45'
--------------开始查询--------------------------
DECLARE @s VARCHAR(1000)
SET @s= '2,34,45,345,867,4,984'
set @s = 'select name='''+replace(@s,',',''''+' union all select ''')+''''
set @s='select name into #t from ('+@s+') a
;with f as
(
Select
a.id,IndexArr=substring(a.IndexArr,b.number,charindex('','',a.IndexArr+'','',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type=''p'' AND B.number BETWEEN 1 AND LEN(A.IndexArr)
where
substring('',''+a.IndexArr,b.number,1)='',''
)
, f2 as
(
select id,count(1) as num from f t where exists(select 1 from #t where name=t.IndexArr) group by id
)
select a.* from tb a ,f2 b where len(a.IndexArr)-len(replace(a.IndexArr,'','',''''))+1=b.num
drop table #t'
EXEC(@s)
----------------结果----------------------------
/* ID IndexArr
----------- -------------------
4 2,34,45
(1 行受影响)
*/
--1.函数
if exists(select * from sys.objects where name&