无自查询实现image表条件排序：全sortkey为0按imageid，否则按sortkey升序

我明白你的需求了：根据sortkey的整体情况动态调整排序规则——当所有sortkey都是0时按imageid降序，存在非0值时先按sortkey升序再按imageid升序。你的两条SQL没生效是因为CASE表达式的语法用错了，而且没处理全局判断的逻辑。

正确的单条SQL语句 #

SELECT * 
FROM image 
WHERE imageobjectid = 1 AND imagetype = 12 
ORDER BY
    -- 判断当前查询范围内是否存在非0的sortkey，存在则按sortkey升序，否则统一用0让第二排序项生效
    CASE WHEN MAX(sortkey) OVER (PARTITION BY imageobjectid, imagetype) > 0 THEN sortkey ELSE 0 END ASC,
    -- 全0时按imageid降序（用-imageid升序模拟），非0时按imageid升序
    CASE WHEN MAX(sortkey) OVER (PARTITION BY imageobjectid, imagetype) = 0 THEN -imageid ELSE imageid END ASC
LIMIT 1;

为什么这个能生效？ #

• 窗口函数MAX(sortkey) OVER (...)：它会在当前筛选的imageobjectid=1 AND imagetype=12范围内计算最大的sortkey值，帮我们判断是否存在非0的sortkey，完全不需要子查询。
• 第一个ORDER BY项：当存在非0值时，按sortkey升序排列；全0时所有行的这个值都是0，不会影响排序，交给第二个项处理。
• 第二个ORDER BY项：全0时用-imageid升序，等价于imageid降序（比如-3 < -2 < -1，对应imageid=3>2>1）；非0时直接按imageid升序排列。

对应测试用例验证 #

1. 测试用例1（全0）：所有行的第一个排序项都是0，第二个项是-imageid，升序后第一个是imageid=3，符合期望。
2. 测试用例2（含sortkey=1）：第一个项按sortkey升序，sortkey=0的行排在前面，第二个项按imageid升序，所以imageid=1排在第一个，符合期望。
3. 测试用例3（含sortkey=1、2）：第一个项按sortkey升序，sortkey=0的imageid=3排在最前面，符合期望。

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