sql 查询条件为拼接字符串 不能使用IN 使用patindex查询结果集

题目： 求组织机构ID在(‘5dc8de20-9f2f-465e-afcc-f69abecaee50′,’63549b63-1e0d-4269-98f4-013869d7f211′,’f7316bf3-38e9-47d4-ab95-8c702b468a2e’,’61e381d1-c8fc-4276-97e0-3f1b6a0356f5′) 中的所有机构。

错误写法：

select * from dd_Report where 1=1 and
OrganizationID in
(‘5dc8de20-9f2f-465e-afcc-f69abecaee50′,’63549b63-1e0d-4269-98f4-013869d7f211′,’f7316bf3-38e9-47d4-ab95-8c702b468a2e’,’61e381d1-c8fc-4276-97e0-3f1b6a0356f5′)
and ReportType=’1′ and DeleteMark =’ 1′

将无法查到（63549b63-1e0d-4269-98f4-013869d7f211,5dc8de20-9f2f-465e-afcc-f69abecaee50）这条记录

正确写法：

思路：求OrganizationID与(‘5dc8de20-9f2f-465e-afcc-f69abecaee50′,’63549b63-1e0d-4269-98f4-013869d7f211′,’f7316bf3-38e9-47d4-ab95-8c702b468a2e’,’61e381d1-c8fc-4276-97e0-3f1b6a0356f5′) 的交集数量。

select *
from dd_Report 
where 1=1 and ReportType=’1′ and DeleteMark =’ 1′ and

dbo.splitstring(”” + replace(OrganizationID,’,’,”’,”’) + ””,

”’5dc8de20-9f2f-465e-afcc-f69abecaee50”,”63549b63-1e0d-4269-98f4-013869d7f211”,”f7316bf3-38e9-47d4-ab95-8c702b468a2e”,”61e381d1-c8fc-4276-97e0-3f1b6a0356f5”’ 
) >0

创建函数

 1 USE [CT_DD]
 2 GO
 3 /****** Object: UserDefinedFunction [dbo].[f_splitSTR] Script Date: 05/20/2016 11:35:44 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER FUNCTION [dbo].[f_splitSTR](
 9 @s varchar(max), --待分拆的字符串
10 @split varchar(10) --数据分隔符
11 )RETURNS @re TABLE(col varchar(100))
12 AS
13 BEGIN
14 DECLARE @splitlen int
15 SET @splitlen=LEN(@split+'a')-2
16 WHILE CHARINDEX(@split,@s)>0
17 BEGIN
18 INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
19 SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
20 END
21 INSERT @re VALUES(@s)
22 RETURN
23 END

•  1 USE [CT_DD]
   2 GO
   3 /****** Object:  UserDefinedFunction [dbo].[splitstring]    Script Date: 05/20/2016 11:38:43 ******/
   4 SET ANSI_NULLS ON
   5 GO
   6 SET QUOTED_IDENTIFIER ON
   7 GO
   8 ALTER function [dbo].[splitstring]
   9 (
  10 @str1 varchar(max),
  11 @str2 varchar(max)
  12 )
  13 returns  int
  14
  15 begin
  16 declare @num int;set @num=0;
  17
  18 select @num=COUNT(1) from  f_splitSTR (@str1,',') temp where col in  (select * from  f_splitSTR (@str2,',') temp2)
  19
  20  return @num;
  21
  22 end

输出结果：