SQL查询使用ORDER BY同时对两列降序排序不生效如何解决

问题核心原因 #

多字段ORDER BY的排序逻辑为按书写顺序依次生效：只有前一个排序字段的取值完全相同时，后一个字段的排序规则才会触发。你当前的写法优先对supporting_actors.Payment_amount（配角片酬）降序，仅当两条记录的配角片酬完全一致时，主角片酬的降序规则才会生效，这就是你感觉只有一列排序生效的根本原因。

适配不同需求的修改方案 #

方案1：优先按主角片酬降序，同主角片酬下再按配角片酬降序 #

如果你的预期是主角片酬高的记录排在最前，同一主角片酬的记录中再按配角片酬从高到低排序，调整ORDER BY的字段顺序即可：

SELECT main_actor.First_Name,
        main_actor.Last_Name,
        main_actor.Payment_amount AS MAIN_ACTOR_PAYMENT,
        supporting_actors.First_Name,
        supporting_actors.Last_Name,
        supporting_actors.Payment_amount AS SUPPORTING_ACTOR_PAYMENT
FROM main_actor
INNER JOIN supporting_actors USING(MOVIE_ID)
ORDER BY main_actor.Payment_amount DESC, supporting_actors.Payment_amount DESC;

方案2：双字段同时参与排序权重计算 #

如果你的预期是两个片酬字段的降序优先级同时生效，可按两个字段的综合值排序，比如按总片酬降序：

SELECT main_actor.First_Name,
        main_actor.Last_Name,
        main_actor.Payment_amount AS MAIN_ACTOR_PAYMENT,
        supporting_actors.First_Name,
        supporting_actors.Last_Name,
        supporting_actors.Payment_amount AS SUPPORTING_ACTOR_PAYMENT
FROM main_actor
INNER JOIN supporting_actors USING(MOVIE_ID)
ORDER BY (main_actor.Payment_amount + supporting_actors.Payment_amount) DESC;

如果需要给两个字段设置不同权重（比如主角片酬权重更高），可使用加权计算规则：

ORDER BY (main_actor.Payment_amount * 0.7 + supporting_actors.Payment_amount * 0.3) DESC;

内容的提问来源于stack exchange，提问作者ryan