SQL Server 2014：店铺列复用首次计数的两种实现方案（含/不含临时表）

原始数据（Table A） #

预期结果（按amount降序排序） #

需求说明 #

按amount降序排序后，为每个shop分配其首次出现时的序号（对应原始表count列的首次值），后续该shop再次出现时直接复用这个序号。此前尝试ROW_NUMBER() OVER (ORDER BY amount DESC)和DENSE_RANK() OVER (PARTITION BY shop ORDER BY amount DESC)均无法满足需求。

实现方案 #

方案一：不使用临时表 #

利用CTE先提取每个店铺的首次序号，再关联原表得到结果：

WITH ShopFirstRank AS (
    SELECT 
        shop,
        ROW_NUMBER() OVER (ORDER BY MAX(amount) DESC) AS expected_result
    FROM TableA
    GROUP BY shop
)
SELECT 
    t.shop,
    t.amount,
    sfr.expected_result
FROM TableA t
INNER JOIN ShopFirstRank sfr 
    ON t.shop = sfr.shop
ORDER BY t.amount DESC;

逻辑说明：通过GROUP BY shop获取每个店铺的最大amount（即首次出现的记录，因为按amount降序），再用ROW_NUMBER()为这些店铺按amount降序分配唯一序号，最后关联原表得到所有行的结果。

方案二：使用临时表 #

拆分逻辑，先将店铺首次序号存入临时表，再关联查询：

-- 创建临时表存储店铺对应的首次序号
CREATE TABLE #ShopFirstRank (
    shop VARCHAR(20),
    expected_result INT
);

-- 插入每个店铺的首次序号
INSERT INTO #ShopFirstRank
SELECT 
    shop,
    ROW_NUMBER() OVER (ORDER BY MAX(amount) DESC) AS expected_result
FROM TableA
GROUP BY shop;

-- 查询最终结果
SELECT 
    t.shop,
    t.amount,
    sfr.expected_result
FROM TableA t
INNER JOIN #ShopFirstRank sfr 
    ON t.shop = sfr.shop
ORDER BY t.amount DESC;

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

逻辑说明：先将每个店铺的首次序号存入临时表，再通过关联临时表获取所有行的结果，适合数据量较大时拆分逻辑、提升可读性。

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