求助：为重复PAN编号设置差异化REMARKS值的SQL实现

为同一PAN编号的记录设置REMARKS字段：

• 每个PAN编号的第一条记录，REMARKS设为 'The One'
• 该PAN编号的其余所有重复记录，REMARKS设为 '0'

预期输出 #

尝试过的无效SQL #

-- 第一个查询
UPDATE YourTableName
SET REMARKS = CASE 
                 WHEN (
                    SELECT COUNT(*)
                    FROM YourTableName AS T2
                    WHERE T2.PAN = YourTableName.PAN AND T2.PAN <> ''
                 ) = 0 THEN 'The One'
                 ELSE '0'
             END;

-- 第二个查询
WITH CTE AS (
    SELECT PAN, ROW_NUMBER() OVER (PARTITION BY PAN ORDER BY (SELECT 0)) AS RowNum
    FROM YourTableName
)
UPDATE YourTableName
SET REMARKS = CASE WHEN CTE.RowNum = 1 THEN 'The One' ELSE '0' END
FROM YourTableName 
INNER JOIN CTE ON YourTableName.PAN = CTE.PAN

-- 第三个查询
UPDATE x
SET x.Total_Outstanding = '0',x.Total_exposure='0'
FROM (
      SELECT ROW_NUMBER() OVER (
      PARTITION BY PAN_NO
      ORDER BY PAN_NO
   ) row_num,
   [Family_Name]
      ,[Client_Name]
      ,[Account_Name]
      ,[Account_Id]
      ,[Held_Away]
      ,[Prospect]
      ,[Product_Name]
      ,[Asset_Name]
      ,[AMC_Short_Name]
      ,[Total_exposure]
      ,[Instrument_Category_Name]
      ,[Instrument_Name]
      ,[ISIN]
      ,[BOS_Code]
      ,[Total_Outstanding]
      ,[PAN_NO]
      ,[Folio]
      ,[Concatenate1]
      ,[Units]
      FROM dtpandata
      ) x where x.row_num > 1

正确的SQL实现方案 #

方案1：带主键的精准更新（推荐） #

如果表有唯一主键（比如ID），用此方案可精准匹配每条记录：

WITH RankedRecords AS (
    SELECT 
        ID, -- 替换为你的表的实际主键字段
        PAN,
        ROW_NUMBER() OVER (PARTITION BY PAN ORDER BY (SELECT 0)) AS RowNum
    FROM YourTableName
)
UPDATE YourTableName
SET REMARKS = CASE WHEN RankedRecords.RowNum = 1 THEN 'The One' ELSE '0' END
FROM YourTableName
INNER JOIN RankedRecords ON YourTableName.ID = RankedRecords.ID

方案2：无主键时的直接更新（SQL Server兼容） #

若表无主键，可直接更新CTE中的记录：

WITH RankedRecords AS (
    SELECT 
        PAN,
        REMARKS,
        ROW_NUMBER() OVER (PARTITION BY PAN ORDER BY (SELECT 0)) AS RowNum
    FROM YourTableName
)
UPDATE RankedRecords
SET REMARKS = CASE WHEN RowNum = 1 THEN 'The One' ELSE '0' END

方案3：MySQL兼容版本 #

MySQL需使用变量或JOIN方式实现：

UPDATE YourTableName t1
JOIN (
    SELECT 
        PAN,
        @row_num := CASE WHEN @prev_pan = PAN THEN @row_num + 1 ELSE 1 END AS RowNum,
        @prev_pan := PAN
    FROM YourTableName
    ORDER BY PAN
) t2 ON t1.PAN = t2.PAN
SET t1.REMARKS = CASE WHEN t2.RowNum = 1 THEN 'The One' ELSE '0' END;

无效查询问题分析 #

1. 第一个查询：逻辑完全错误，COUNT(*)=0表示当前PAN无匹配记录，和需求中标记首个重复项的逻辑相反。
2. 第二个查询：仅通过PAN关联会导致同PAN的所有记录被统一赋值，无法区分单条记录的序号。
3. 第三个查询：更新的字段是Total_Outstanding和Total_exposure，而非需求中的REMARKS，且未处理首个记录的The One赋值。

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