基于 SQL 管理器中的 IF 语句设置变量

Question

我有一个名为Ordhdr的表，我想根据该表的[status]列创建一个查询。 我有许多状态，我想将其分组为"赢"、"输"、"待定"，以便于查看。 如何设置我的声明，将语句设置为首先将当前状态"重命名"为 WON、LOST 或 PENDING，以便我可以使用新名称显示我的查询结果。

Sample Data
Order 1, STatus is QteCall1 (Pending Status)
Order 2, Status is Converted (Won Status)
Order 3, STatus is Declined (Dead Status)
Order 4, Status is Waiting (Pending Status)
Order 5, Status is NOResponse (Dead Status)'

我将如何"重命名"当前状态以获得结果显示计数

• 1 韩元
• 2 待定
• 2 死亡

Answer 1

这是你需要的吗？

SELECT CASE WHEN STATUS = 'Converted' THEN 'WON' 
            WHEN STATUS IN ('QteCall1','Waiting') THEN 'PENDING'
            WHEN STATUS IN ('Declined', 'NOResponse ') THEN 'LOST' END AS NEW_NAME
            --The above case expression is being used to compare and rename the values according to the desired match
        ,COUNT(*) AS COUNT
    FROM Ordhdr
    GROUP BY 
        CASE WHEN STATUS = 'Converted' THEN 'WON' 
            WHEN STATUS IN ('QteCall1','Waiting') THEN 'PENDING'
            WHEN STATUS IN ('Declined', 'NOResponse ') THEN 'LOST' END
            -- This second case expression is the same as the previous one, but it is required by the SQL standards to be in the GROUP BY section

查询的结果。