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

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

输出结果：