对每个case-when语句进行排序

Question

这是我的用户数据库：

id | sex    | trending | date_registered
---+--------+----------+----------------
1  | male   | 1        | 29-04-2020
2  | male   | 1        | 28-04-2020
3  | male   | 0        | 27-04-2020
4  | female | 1        | 26-04-2020
5  | female | 1        | 25-04-2020
6  | female | 0        | 24-04-2020
7  | female | 0        | 23-04-2020
8  | male   | 1        | 22-04-2020
9  | male   | 0        | 21-04-2020

我使用以下查询来返回：

(1)潮流女性

(2)其余女性

(3)流行男性

(4)其余男性

select
    id,
    sex,
    trending,
    date_registered
from users
order by
    case
        when sex = 'female' and trending = 1 then 1
        when sex = 'female' and trending = 0 then 2
        when sex = 'male' and trending = 1 then 3
        when sex = 'male' and trending = 0 then 4
    end 

here is the demo

最重要的是，对于每个case-when语句，我希望首先按最近的用户进行排序。

例如，when sex = 'female' and trending = 1 then 1返回一个ASC排序，因此最老的用户将首先显示。

当我尝试对case进行DESC排序时，像bellow这样的语句不会改变任何事情：

order by
(CASE when sex = 'female' and trending = 1 then 1 END) DESC,
(CASE when sex = 'female' and trending = 0 then 2 END) DESC,
(CASE when sex = 'male' and trending = 1 then 3 END) DESC,
(CASE when sex = 'male' and trending = 0 then 4 END) DESC

Answer 1

根据您的需求，您需要按sex升序(女性在前)、trending降序(趋势用户在前)和date_registered降序(最近用户在前)进行排序：

select
    id,
    sex,
    trending,
    date_registered
from users
order by sex, trending desc, date_registered desc

小提琴的输出：

id  sex     trending    date_registered
4   female  1           26-04-2020
5   female  1           25-04-2020
6   female  0           24-04-2020
7   female  0           23-04-2020
1   male    1           29-04-2020
2   male    1           28-04-2020
8   male    1           22-04-2020
3   male    0           27-04-2020
9   male    0           21-04-2020

Updated demo

Answer 2

我认为你可以这样写：

order by sex, trending desc

Here是您的db<>fiddle。

Answer 3

仅将DESC放在CASE块之后( END关键字之后)：

select
    id,
    sex,
    trending,
    date_registered
from users
order by
    case
        when sex = 'female' and trending = 1 then 1
        when sex = 'female' and trending = 0 then 2
        when sex = 'male' and trending = 1 then 3
        when sex = 'male' and trending = 0 then 4
    end DESC