多条记录合并为一条，同时将连续的编号写成“起始编号-结束编号”的形式

最新推荐文章于 2022-02-23 09:47:44 发布

jidianxueyuan

最新推荐文章于 2022-02-23 09:47:44 发布

阅读量748

点赞数 1

文章标签： function table insert path xml sql

本文介绍了一种使用SQL Server 2005及2000的方法来合并连续的序列号，并将其简化为范围表示形式，例如从单独列出的连续编号转换为起始和结束编号的组合。这种方法可以有效地减少数据展示的冗余。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql2005

DECLARE @tb TABLE (编号 varchar(6),值 int)
INSERT INTO @tb
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1

--SQL查询如下:

;WITH Liang AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY 值 ORDER BY 值) - 编号 AS rowid,*
    FROM @tb
),
Liang2 AS
(
    SELECT 值,rowid,CASE WHEN COUNT(*) > 1 THEN RTRIM(MIN(编号))+'~'+RTRIM(MAX(编号))
                           ELSE RTRIM(MIN(编号)) END AS flag
    FROM Liang
    GROUP BY 值,rowid
)
SELECT
    STUFF((SELECT ',' + flag AS [text()] FROM Liang2
     WHERE 值 = A.值 ORDER BY ABS(rowid) FOR XML PATH('')),1,1,'') AS 编号,
    值
FROM Liang2 AS A
GROUP BY 值

/*
编号    值
000066,000600    1
000055,000057,000059~000064    2
000065    3
*/

sql 2000

--SQL查询如下:

GO
CREATE FUNCTION dbo.MergeNo(@值 int)
    RETURNS varchar(8000)
AS
BEGIN
    DECLARE @re varchar(8000);
    SET @re = '';

    SELECT
        @re = @re + CASE WHEN EXISTS(SELECT * FROM tb
                                     WHERE 值 = A.值
                                         AND CAST(编号 AS int) -1 = CAST(A.编号 AS int))
                            THEN
                               CASE WHEN RIGHT(@re,1) <> '~'
                                       THEN ',' + 编号 + '~'
                               ELSE '' END
                         ELSE
                             CASE WHEN EXISTS(SELECT * FROM tb
                                              WHERE 值 = A.值
                                                AND CAST(编号 AS int) + 1 = CAST(A.编号 AS int))
                                     THEN 编号
                                  ELSE ',' + 编号 END
                    END
    FROM tb AS A WHERE 值 = @值
    ORDER BY 编号;

    RETURN STUFF(@re,1,1,'');
END
GO

SELECT 值 ,dbo.MergeNo(值) AS 编号
FROM tb
GROUP BY 值
GO
DROP TABLE tb
DROP FUNCTION dbo.MergeNo