postgreOrder按超过2的大小写排序

Question

我尝试下面这个查询，用ORDER BY CASE和5 WHEN CONDITIONS排序table1，并得到了意想不到的结果。看来WHEN condition number 2, 3 and 4不工作了。我不知道这是怎么回事，需要帮助吗?非常感谢!

SELECT s.so_id, t.affiliate_id, t.aff_priority, t.priority_type
FROM table1 s
LEFT JOIN table2 f ON f.id = s.id
LEFT JOIN table3 t ON t.affiliate_id = f.affiliate_id
ORDER BY CASE
WHEN t.is_active = 1 THEN 0
WHEN t.aff_priority IS NOT NULL THEN 1
WHEN t.priority_type = 1 THEN 2
WHEN t.priority_type = 2 THEN 3
WHEN t.priority_type = 3 THEN 4 END,
t.aff_priority DESC, s.so_id DESC

Answer 1

据我所知，正确的订单条件如下我希望它能帮助你。

ORDER BY CASE WHEN t.is_active = 1 THEN t.is_active END ASC,
CASE WHEN t.aff_priority IS NOT NULL THEN t.aff_priority END ASC,
CASE WHEN t.priority_type = 1 THEN 1
WHEN t.priority_type = 2 THEN 2
WHEN t.priority_type = 3 THEN 3 END ASC,
t.aff_priority DESC, 
s.so_id DESC