sql - TSQL 填充数据

Question

我需要找到一种方法以特定方式Group By一些数据。

id  Number  FROM    TO
1   240     201710  201712
1   240     201712  201801
1   240     201801  201803
1   300     201803  201805
1   240     201805  999999

我需要以这种方式对它们进行分组:

id  TEXT
1   201710 - 201803: 240, 201803 - 20180: 300, 201805 - 999999: 240

我的问题是获得正确的分组。它必须将第 1-3 行分组并将 FROM 设置为 201710 并将 TO 设置为 201803。但我不能按数字分组，因为它稍后可能会再次更改。

有什么想法吗？

我唯一的想法是检查下一行的 FROM 和 THE FROM，如果数字匹配，则将其设置为上一个 FROM。然后我可以按 id、number 和 FROM 进行分组，并采用 TO 的 MAX。但我真的不想使用 Cursor。

这里是一个测试脚本:

DECLARE @Test AS table
(
    customerid int,
    Number     int,
    DateFrom   int,
    DateTo     int
);

INSERT INTO @Test
VALUES (1, 240, 201710, 201712),
       (1, 240, 201712, 201801),
       (1, 240, 201801, 201803),
       (1, 300, 201803, 201805),
       (1, 240, 201805, 999999);

SELECT t.customerid,
       t.Number,
       t.DateFrom,
       t.DateTo
FROM   @Test t;

SELECT   customerid,
         STUFF(
         (SELECT DISTINCT
                 ', ' + CONVERT(varchar(100), (t2.DateFrom)) + ' - ' + CONVERT(varchar(100), (t2.DateTo)) + ' : '
                 + CONVERT(varchar(100), (t2.Number))
          FROM   @Test t2
         FOR XML PATH('')),
         1,
         2,
         '') AS Text
FROM     @Test t
WHERE t.customerid = t2.customerid
GROUP BY t.customerid;

Answer 1

试试这个:

;WITH cte AS (
    SELECT t.customerid,
           t.Number,
           t.DateFrom,
           t.DateTo,
           ROW_NUMBER() over (order by datefrom) - ROW_NUMBER() over (partition by number order by datefrom) grpCol
    FROM   @Test t
), cte2 AS (
    SELECT customerid, 
           cast(MIN(datefrom) as varchar(10)) + ' - ' + cast(MAX(dateto) as varchar(10)) + ': ' + cast(number as varchar(10)) [text],
           MIN(datefrom) datefrom
    FROM cte c
    group by customerid, number, grpCol
)

SELECT customerid, 
       (select [text] + ', ' from cte2 where customerid = c.customerid order by datefrom for xml path('')) [text]
FROM cte2 c
GROUP BY customerid

输出:

customerid | text
1          | 201710 - 201803: 240, 201803 - 201805: 300, 201805 - 999999: 240,