如何新增Type列，按DateCreated排序并对同SupplierNumber+DateCreated记录分组

需求说明 #

现有包含10列专属信息的Supplier表，需实现以下效果：

• 按记录创建时间DateCreated排序
• 将具有相同SupplierNumber和DateCreated的记录归为一组
• 新增Type列，以Group 1、Group 2……的格式标记每个分组

样例表数据 #

SupplierName    SupplierNumber  DateCreated
Supplier4       50006155        07/13/2022 08:09PM
Supplier1       50000253        07/18/2022 10:19PM
Supplier5       50003200        07/13/2022 08:23PM
Supplier1       50000253        07/18/2022 10:19PM
Supplier3       50005963        07/13/2022 08:06PM
Supplier2       50001781        07/20/2022 02:11PM
Supplier3       50005963        07/13/2022 08:06PM
Supplier4       50006155        07/13/2022 08:09PM
Supplier5       50003200        07/13/2022 08:23PM
Supplier2       50001781        07/20/2022 02:11PM

期望输出格式 #

Type        SupplierName    SupplierNumber  DateCreated
Group 1     Supplier1       50000253        07/18/2022 10:19PM
Group 1     Supplier1       50000253        07/18/2022 10:19PM
Group 2     Supplier2       50001781        07/20/2022 02:11PM
Group 2     Supplier2       50001781        07/20/2022 02:11PM
Group 3     Supplier3       50005963        07/13/2022 08:06PM
Group 3     Supplier3       50005963        07/13/2022 08:06PM
Group 4     Supplier4       50006155        07/13/2022 08:09PM
Group 4     Supplier4       50006155        07/13/2022 08:09PM
Group 5     Supplier5       50003200        07/13/2022 08:23PM
Group 5     Supplier5       50003200        07/13/2022 08:23PM

已尝试的解决方案 #

Select SupplierNumber,DateCreated  from Supplier
GROUP BY SupplierNumber,DateCreated
ORDER BY DateCreated, SupplierNumber

正确解决方案 #

使用DENSE_RANK()窗口函数为每个唯一的(SupplierNumber, DateCreated)组合生成连续的分组编号，再拼接成Group X格式的Type列。同时保留表中所有10列数据，按需求排序：

匹配样例输出顺序（按供应商名称分组编号）

SELECT
    CONCAT('Group ', DENSE_RANK() OVER (ORDER BY SupplierName)) AS Type,
    SupplierName,
    SupplierNumber,
    DateCreated
    -- 此处添加表中剩余的8列，例如：Column1, Column2, ..., Column8
FROM Supplier
ORDER BY
    DENSE_RANK() OVER (ORDER BY SupplierName),
    DateCreated;

按创建时间排序生成分组编号

若需严格按DateCreated先后顺序生成分组编号，可调整窗口函数的排序条件：

SELECT
    CONCAT('Group ', DENSE_RANK() OVER (ORDER BY DateCreated, SupplierNumber)) AS Type,
    SupplierName,
    SupplierNumber,
    DateCreated
    -- 此处添加表中剩余的8列
FROM Supplier
ORDER BY
    DateCreated,
    SupplierNumber;

说明：DENSE_RANK()会为相同分组的记录分配相同的编号，且编号连续不中断，完美匹配分组标记需求。该语法适用于SQL Server、MySQL 8.0+、Oracle等支持窗口函数的数据库。

内容的提问来源于stack exchange，提问作者T S Nandakumar