MS SQL 2014排名需求：按规则为客户设备生成发票分组排名

我来帮你搞定这个发票排名的需求！根据你描述的规则——每个客户对应发票组，SeparateInv=1的设备单独生成一张发票，我们可以用SQL的窗口函数结合分组逻辑来实现，下面是完整的步骤和代码：

核心逻辑梳理 #

• 对于SeparateInv = 1的设备：每台设备单独占用一个发票编号（排名）
• 对于SeparateInv = 0的设备：同一客户下的所有这类设备共享同一个发票编号

完整SQL实现 #

首先补全你提供的临时表脚本，然后添加排名逻辑：

-- 创建临时表并插入示例数据
CREATE TABLE #Tmp (DeviceId INT, CustomerId INT, SeparateInv TINYINT)
INSERT INTO #Tmp VALUES (10, 1, 0)
INSERT INTO #Tmp VALUES (11, 1, 0)
INSERT INTO #Tmp VALUES (12, 1, 1)
INSERT INTO #Tmp VALUES (13, 1, 1)
INSERT INTO #Tmp VALUES (17, 2, 0)
INSERT INTO #Tmp VALUES (19, 2, 0)

-- 生成发票排名的CTE查询
WITH InvoiceGrouping AS (
    SELECT 
        DeviceId,
        CustomerId,
        SeparateInv,
        -- 构建分组标识：单独发票的设备用【客户ID_设备ID】作为唯一组，非单独发票用【客户ID_0】作为公共组
        CASE 
            WHEN SeparateInv = 1 THEN CONCAT(CustomerId, '_', DeviceId)
            ELSE CONCAT(CustomerId, '_', '0')
        END AS GroupIdentifier
    FROM #Tmp
)
SELECT 
    DeviceId,
    CustomerId,
    SeparateInv,
    -- 在每个客户范围内，对分组标识进行排名，得到发票序号
    DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY GroupIdentifier) AS InvoiceRank
FROM InvoiceGrouping
ORDER BY CustomerId, InvoiceRank;

-- 清理临时表
DROP TABLE #Tmp;

预期执行结果 #

运行上述代码后，你会得到如下结果，完全符合规则要求：

代码解释 #

1. GroupIdentifier字段：通过CASE表达式区分两种设备的分组规则，确保单独发票的设备每组仅包含自身，非单独发票的设备同客户归为一组。
2. DENSE_RANK()窗口函数：在每个客户的分区内，对分组标识进行排名，这样同一组的设备会得到相同的发票排名，单独设备则依次获得递增的排名。

如果你需要将排名转换为全局唯一的发票编号（比如跨客户的连续编号），只需要去掉PARTITION BY CustomerId，改用全局的DENSE_RANK()即可。

内容的提问来源于stack exchange，提问作者Przemek