SQL根据分隔的字符串，查询并组合查询结果的解决方案(转)

-------------------------(一)准备工作-------------------------

--CREATE TABLE CUSTOMER
CREATE TABLE CUSTOMER
(
ID INT PRIMARY KEY,
CUSTOMER_NAME NVARCHAR(50)
)

--CREATE TABLE PRODUCTION
CREATE TABLE PRODUCTION
(
ID INT PRIMARY KEY,
PRODUCTION_NAME NVARCHAR(100),
CUSTOMER_ID_COLLECTIONS NVARCHAR(100)
)

--INSERT INIT DATA INTO TABLE CUSTOMER
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(1, 'Kim');
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(2, 'Leo');
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(3, 'Echo');
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(4, 'Felix');
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(5, 'Kelly');
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(6, 'Linda');
INSERT INTO CUSTOMER(ID, CUSTOMER_NAME) VALUES(7, 'Vincent');

--INSERT INIT DATA INTO TABLE PRODUCTION
INSERT INTO PRODUCTION(ID, PRODUCTION_NAME, CUSTOMER_ID_COLLECTIONS)VALUES(1, 'OFFICE 2003', NULL);
INSERT INTO PRODUCTION(ID, PRODUCTION_NAME, CUSTOMER_ID_COLLECTIONS)VALUES(2, 'BMW X5', '4');
INSERT INTO PRODUCTION(ID, PRODUCTION_NAME, CUSTOMER_ID_COLLECTIONS)VALUES(3, 'Chocolate', '2,4');
INSERT INTO PRODUCTION(ID, PRODUCTION_NAME, CUSTOMER_ID_COLLECTIONS)VALUES(4, 'Box360', '1,2,5,6');

-------------------------(二)任务-------------------------

--查询所有商品相应的客人名字列表，用逗号分隔
--如，“商品A”的客人ID集合为“2, 3”。
--则要查找ID为2和3的客人名字，并通过逗号重新组合成字符串。如："客人2的名字, 客人3的名字"

 

-------------------------(三)解决方案-------------------------

--通过两个Function实现，
--第一个Function要求能够解析分隔的ID字符串并返回TABLE。
--第二个Function要根据返回的TABLE，遍历并查询，然后将查询结果用逗号分隔符进行组合。

--(3.1) CREATE FUNCTION f_split
CREATE function f_split(@str varchar(8000))
returns @tb table(num varchar(100))
as
begin
      while charindex(',',@str)>0
      begin
            insert @tb
            select left(@str,charindex(',',@str)-1)

            set @str=stuff(@str,1,charindex(',',@str),'')
      end
      
      insert @tb select @str
      return
end

--(3.2) CREATE FUNCTION f_getCustomerList
CREATE FUNCTION f_getCustomerList(@SplitIdStr NVARCHAR(2000))
RETURNS NVARCHAR(2000)
AS
BEGIN
    DECLARE @result VARCHAR(2000)
    SET @result=''
    DECLARE CURSOR_GET_NAME CURSOR
    FOR
        SELECT * FROM f_split(@SplitIdStr)
    OPEN CURSOR_GET_NAME

    DECLARE @Id SYSNAME
    
    FETCH FROM CURSOR_GET_NAME INTO @Id
    WHILE @@fetch_status=0
    BEGIN
        SET @result=@result+(SELECT CUSTOMER_NAME FROM CUSTOMER WHERE ID = @Id)+','
        FETCH FROM CURSOR_GET_NAME INTO @Id
    END
    CLOSE CURSOR_GET_NAME
    SET @result= substring(@result,0,len(@result))
    
    DEALLOCATE CURSOR_GET_NAME

    RETURN @result
END

-------------------------(四)搞定，撒花ING-------------------------

SELECT PRODUCTION.PRODUCTION_NAME, dbo.f_getCustomerList(PRODUCTION.CUSTOMER_ID_COLLECTIONS) AS [CUSTOMER LIST] FROM PRODUCTION 

 

转载于:https://www.cnblogs.com/szjdw/archive/2012/05/19/2509085.html