mysql - sql order by 子句中的多种情况条件？

Question

如何对两列或更多列进行条件排序..我刚刚尝试了下面的代码。有没有更好的写法？

CASE WHEN  d.receipt IS NULL AND c.receipt =1 then pr.account END ASC,
CASE WHEN  d.receipt =1 AND c.receipt IS NULL then pr.account END ASC,
CASE WHEN  d.receipt =1 AND c.receipt =0 then pr.account END ASC,
CASE WHEN  d.receipt =1 AND c.receipt =1 then pr.account END ASC,

CASE WHEN  d.receipt IS NULL AND c.receipt IS NULL THEN pr.amount END DESC,
CASE WHEN  d.receipt IS NULL AND c.receipt =0 THEN pr.amount END DESC,
CASE WHEN  d.receipt =0 AND c.receipt IS NULL THEN pr.amount END DESC,
CASE WHEN  d.receipt =0 AND c.receipt =0 THEN pr.amount END DESC,
CASE WHEN  d.receipt =0 AND c.receipt =1 THEN pr.amount END DESC

Answer 1

你可以试试这个:

CASE WHEN d.receipt = 1 THEN pr.account
     WHEN d.receipt = 0 THEN -pr.amount
     WHEN c.receipt = 1 THEN pr.account
     ELSE                    -pr.amount
END

它总是按升序排序，但在某些情况下按负数排序，这当然会导致降序排序。

手册https://dev.mysql.com/doc/refman/5.7/en/case.html说:

“每个 WHEN 子句 search_condition 表达式都会被求值，直到其中一个为 true，此时执行其对应的 THEN 子句 statements_list。如果没有相等的 search_condition，则执行 ELSE 子句 statements_list(如果有)。”

这意味着表达式按照指定的顺序进行计算。
换句话说，仅当 d.receipt 为 NULL 时，才会评估第三个条件 (c.receipt = 1)。