从表保存了主表的id，以分号分隔，怎么样用一条sql搞定主表满足条件的查询？ 不希望单独写存储过程，或者后台拆成int后传进来，就一条sql 搞定，一条...

问题描述：在数据库的从表里边用;号保存了主表的id，主表id 整型，从表字段string类型，希望写一条sql 查询出主表里边满足条件的数据，不希望通过写独立的存储过程或者后台代码把id 拆开后再查，就一条sql 解决所有问题，终于找到解决办法。

主表数据：

从表数据：

1. 在sql数据库建立SplitStringToTable函数

 1 USE [AutoRent]
 2 GO
 3 
 4 /****** Object:  UserDefinedFunction [dbo].[SplitStringToTable]    Script Date: 08/11/2015 15:07:18 ******/
 5 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitStringToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 6 DROP FUNCTION [dbo].[SplitStringToTable]
 7 GO
 8 
 9 USE [AutoRent]
10 GO
11 
12 /****** Object:  UserDefinedFunction [dbo].[SplitStringToTable]    Script Date: 08/11/2015 15:07:18 ******/
13 SET ANSI_NULLS ON
14 GO
15 
16 SET QUOTED_IDENTIFIER ON
17 GO
18 
19 CREATE  FUNCTION  [dbo].[SplitStringToTable] 
20 (  
21      @String  nvarchar(4000),  --格式如：“1，2，3，4，”
22      @SplitChar  nvarchar(10)  --分割的字符：“，”
23 )  
24 RETURNS    @table  Table(ID  varchar(100))  
25 AS  
26 BEGIN  
27    DECLARE  @Index  INT  
28    SET  @Index  =  0  
29 
30         IF @String <> ''
31         Begin
32             IF RIGHT(@String,1)<> @SplitChar 
33                 SET @String = @String + @SplitChar
34             IF LEFT(@String,1)= @SplitChar 
35             SET @String = STUFF(@String, 1, 1, '')
36         End
37  
38        WHILE  CHARINDEX(@SplitChar,@String,@Index)  >  0    
39        BEGIN  
40            INSERT INTO @table(ID)
41                VALUES (SUBSTRING(@String, @Index, CHARINDEX(@SplitChar, @String,
42                 @Index) - @Index))
43                SET @index = CHARINDEX(@SplitChar, @String, @Index) + 1 END  
44 RETURN  
45 END
46 
47 GO
2  利用函数编程查询语句

1   SELECT  * FROM TB_ProtocolCustomer WHERE ProtocolCustomerID IN (select  b.id
2    FROM  TB_ProtocolCustomerattach a 
3    cross apply dbo.SplitStringToTable(a.attachpcustomer,';')  as b
4    WHERE a.pcustomer=7795)

3 就此问题搞定

 

 

转载于:https://www.cnblogs.com/hbsfgl/p/4721079.html