编写显示订单的查询，并根据特定规范包含排名列

I need help on writing a query that shows the orders and includes a ranking column based on this: - TotalMoney less than 100 = "LOW" - TotalMoney between 100 and 1000 = "MIDDLE" - TotalMoney higher than 1001 AND Order was placed after Mar.22, 2005 = "HIGH" - All others "NOTHING" - Sort results showing first LOW, then MIDDLE , then HIGH

我需要帮助编写一个显示订单的查询,并包含一个基于此的排名列: - TotalMoney小于100 =“LOW” - TotalMoney介于100和1000之间=“MIDDLE” - TotalMoney高于1001 AND Order被放置在Mar之后。22,2005 =“HIGH” - 所有其他“NOTHING” - 排序结果显示第一个低,然后是MIDDLE,然后是HIGH

1 个解决方案

#1

You need to be looking at CASE statements within T-SQL to get what you want out of this.
Example below;

您需要查看T-SQL中的CASE语句,以获得您想要的内容。以下示例;

SELECT *, Ranking =
    CASE 
    WHEN TotalMoney < 100 THEN 'LOW'
    WHEN TotalMoney BETWEEN 100 AND 1000 THEN 'MIDDLE'
    WHEN TotalMoney > 1001 AND OrderDate > '20050322' THEN 'HIGH'
    ELSE 'NOTHING'
    END
FROM Table
ORDER BY CASE 
    WHEN TotalMoney < 100 Then 1
    WHEN TotalMoney BETWEEN 100 AND 1000 Then 2
    WHEN TotalMoney > 1001 AND OrderDate > '20050322' Then 3
    ELSE 4
    END

Edited, as I was previously unaware that you cannot use aliased column names within an ORDER BY CASE statement.

编辑,因为我以前不知道你不能在ORDER BY CASE语句中使用别名列名。

#1