SELECT * FROM emm_task.tbl_scm_passenger ORDER BY CASE WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) then 0 ELSE 1 END, CASE WHEN phone IS NULL THEN 1 WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN 0 ELSE 2 END, phone ASC, CASE WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN mobile END, CASE WHEN mobile IS NULL THEN 1 ELSE 0 END想让mobile排序的时候空值在下面排序
时间: 2023-09-18 16:11:26 浏览: 25
您的查询语句已经实现了 mobile 的排序,并将空值排在了 mobile 值的后面。如果您想要让 mobile 的空值排在 mobile 值的前面,则可以将这一部分代码:
CASE WHEN mobile IS NULL THEN 1 ELSE 0 END
修改为:
CASE WHEN mobile IS NULL THEN 0 ELSE 1 END
这样一来,空值就会优先于非空值进行排序。完整的修改后的查询语句如下所示:
SELECT * FROM emm_task.tbl_scm_passenger
ORDER BY
CASE WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213981,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN 0 ELSE 1 END,
CASE WHEN phone IS NULL THEN 1 WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN 0 ELSE 2 END,
CASE WHEN id IN (53213970,53213971,53213972,53213973,53213974,53213975,53213976,53213977,53213978,53213979,53213980,53213982,53213983,53213984,53213985,53213986,53213987,53213988,53213989) THEN mobile END,
CASE WHEN mobile IS NULL THEN 0 ELSE 1 END,
phone ASC;