--生成已用编号分布字符串的函数

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

--*/