SQL查询到3个不同列上的“ORDER BY”

Given the below table (Raw Table), I need to run a query so that everything is sorted to mirror the Result Table. I have tried using ORDER BY to do this and I can get the first 2 requirements to work (see order by requirements) but the 3rd one is where I'm getting lost.

对于下面的表(原始表)，我需要运行一个查询，以便对所有内容进行排序以反映结果表。我尝试过使用ORDER BY来做这个，我可以让前两个需求工作(参见ORDER BY requirements)，但是第三个是我迷失的地方。

Right now, my query is as follows:

目前，我的疑问如下:

sqlCom.CommandText = "SELECT * FROM Questions WHERE Identifier = @identifier AND Flag <> 'DELETED' ORDER BY Status DESC,  Number * 1";

I have been reading into using a ORDER BY CASE WHEN...THEN...END but I'm not understanding the syntax correctly. I keep getting syntax errors so I'm not even able to run it.

我一直在研究在……结束，但是我没理解正确的语法。我不断地得到语法错误，所以我甚至不能运行它。

ORDER BY Requirement:

订单要求:

Order the status column with 'open' values at top, 'closed' values at bottom (this is working)
将状态列的顶部为“open”，底部为“closed”
Order the Number column to be sequential (with closed at bottom, open at top for the Status column) (this is working)
将数字列按顺序排列(底部为关闭，顶部为状态列为打开)(这是有效的)
Order the Flag column so that null values are at the top and anything else is at the bottom (above Status closed sort)
对标志列进行排序，使null值位于顶部，其他值位于底部(在状态关闭排序之上)

From what I can tell, the ORDER BY CASE is what I want, but I can't seem to get the syntax correct. Thanks in advance for any helpful input.

就我所知，按情况排序是我想要的，但是我似乎不能使语法正确。感谢您的帮助。

RAW TABLE

原始的表

| Flag | Number | Status |
|------------------------|
| a    |    1   |  open  |
|      |    5   |  open  |
|      |    3   | closed |
| a    |    4   |  open  |
| a    |    2   | closed |

RESULT TABLE

结果表

| Flag | Number | Status |
|------------------------|
|  a   |    1   |  open  |
|  a   |    4   |  open  |
|      |    5   |  open  |
|      |    3   | closed |
|  a   |    2   | closed |

2 个解决方案

#1

SELECT * FROM Questions
WHERE Identifier = @identifier AND Flag <> 'DELETED' 
ORDER BY Status DESC,  Number,
(case when flag is null then 1 else 0 end) desc, flag

You can try this.

你可以试试这个。

#2

Using nested CASE expressions:

使用嵌套的情况下表达式:

SELECT * 
FROM Questions
--WHERE Identifier = @identifier AND Flag <> 'DELETED' 
ORDER BY
    CASE WHEN Status = 'Open' THEN 0 ELSE 1 END,
    Number * 
        CASE 
            WHEN Status = 'Closed' THEN
                CASE 
                    WHEN Flag IS NULL THEN 0 
                    ELSE 1 
                END
            ELSE 1
        END,
    Number,
    CASE WHEN Flag IS NULL THEN 1 ELSE 0 END

The idea is to put the rows with Status = 'Closed' and FLAG IS NULL directly below the rows with Status = 'Open'. Then rows where FLAG IS NOT NULL and Status = 'Closed' will be ordered last.

其思想是将状态= 'Closed'和标志为NULL的行放在状态= 'Open'的行下面。然后，标记不为NULL且状态= 'Closed'的行将最后排序。

#1

SELECT * FROM Questions
WHERE Identifier = @identifier AND Flag <> 'DELETED' 
ORDER BY Status DESC,  Number,
(case when flag is null then 1 else 0 end) desc, flag