63.返回应用编号、缺号分布字符串的处理案例

左手的ㄟ右手 2024-02-19 18:00 49阅读 0赞

--生成已用编号分布字符串的函数
    CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
    RETURNS varchar(8000)
    AS
    BEGIN
    	DECLARE @re varchar(8000),@pid int
    	SELECT @re='',@pid=-1
    	SELECT @re=CASE 
    			WHEN col2=@pid+1 THEN @re
    			ELSE @re
    				+CASE 
    					WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
    					ELSE CAST(-@pid as varchar)
    				END
    				+','+CAST(col2 as varchar) 
    			END,
    		@pid=col2
    	FROM tb
    	WHERE col1=@col1
    	ORDER BY col2
    	RETURN(STUFF(@re,1,2,'')
    		+CASE 
    			WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
    			ELSE CAST(-@pid as varchar)
    		END)
    END
    GO
    
    --生成缺号分布字符串的函数
    CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    	DECLARE @re varchar(8000),@pid int
    	SELECT @re='',@pid=0
    	SELECT @re=CASE 
    			WHEN col2=@pid+1 THEN @re
    			ELSE @re+','+CAST(@pid+1 as varchar)
    				+CASE 
    					WHEN @pid+1=col2-1 THEN ''
    					ELSE CAST(1-col2 as varchar)
    				END
    			END,
    		@pid=col2
    	FROM tb
    	WHERE col1=@col1
    	ORDER BY col2
    	RETURN(STUFF(@re,1,1,''))
    END
    GO
    
    --调用测试
    --测试数据
    CREATE TABLE tb(col1 varchar(10),col2 int)
    INSERT tb SELECT 'a',2
    UNION ALL SELECT 'a',3
    UNION ALL SELECT 'a',5
    UNION ALL SELECT 'a',8
    UNION ALL SELECT 'a',9
    UNION ALL SELECT 'b',1
    UNION ALL SELECT 'b',5
    UNION ALL SELECT 'b',6
    UNION ALL SELECT 'b',7
    
    --查询
    SELECT col1,
    	col2_Series=dbo.f_GetStrSeries(col1),
    	col2_Series=dbo.f_GetStrNSeries(col1)
    FROM tb
    GROUP BY col1
    /*--结果
    col1       col2_Series       col2_Series 
    -------------- ------------------------ --------------
    a          2-3,5,8-9        1,4,6-7
    b          1,5-7           2-4
    --*/