sql - 在 Order By 之后添加标记第一行的字段

Question

我有一个看起来有点像这样的 Select 语句（这里简称为它只是从现有表中选择字段，没有什么过于复杂的）

 SELECT  
    CASE 
        WHEN dbo.Account_Inventory.NUMBER IS NULL THEN 'C' + CAST(CAST(dbo.Account_Inventory.CUST_ID AS bigint) AS nvarchar) 
        WHEN dbo.Account_Inventory.CUST_ID IS NULL THEN 'A' + CAST(CAST(dbo.Account_Inventory.ACCT_NUM AS bigint) AS nvarchar) 
        ELSE 'M' + CAST(CAST(dbo.Account_Inventory.NUMBER AS bigint) AS varchar) 
    END AS ID,
    CASE... 
    
...FROM     
    dbo.Account_Inventory LEFT OUTER JOIN dbo.Dorm ON dbo.Account_Inventory.ACCT_NUM = dbo.Dorm.ACCT_NUM
WHERE  
    (dbo.Account_Inventory.ACCT_CLOSE_DT IS NULL) AND 
    (CASE 
        WHEN dbo.Account_Inventory.XYZ = 'Yes' AND dbo.Account_Inventory.BUS_LINE_CDE IN ('BB', 'BBM', 'ABC', 'ABCD') THEN 'ABC' 
        WHEN dbo.Account_Inventory.XYZ = 'YES' THEN 'EFG' 
        ELSE dbo.Account_Inventory.GLOBAL_BUSINESS 
    END IN ('BIG', 'SMAL','ABC')) 
ORDER BY 
    Order By
         ID, dbo.Account_Inventory.INT_DAILY_RATE DESC 

在此之后，我想添加一个字段，它将标记第一条记录（ID 字段）并将其标记为“唯一”，将其他记录标记为“na”。

任何帮助表示赞赏！

标签： sqlsql-server

Answer 1

您可以使用 case 语句。

case when row_number() over (order by ID, Rate desc) =1 then 'Unique' else 'na'