按多个字段排序
按多个字段排序
提问于 2017-11-22 01:14:34
我对订单有以下查询：
ORDER BY FIELD(type, 'b', 'p', 'j', 'i', 'a', 'c', 'v'),
FIELD(sex, 'M', 'F'),
user.name ASC";但我需要下一个订单的结果：
所以我解释错了，我想我对此太厌倦了。我需要得到这样的东西。
order by (case
when type = 'b' and sex = 'M' then 1
when type = 'b' and sex = 'F' then 2
when type = 'p' and sex = 'M' then 3
when type = 'p' and sex = 'H' then 4
when type = 'i' and sex = 'M' then 5
when type = 'a' and sex = 'M' then 6
when type = 'c' and sex = 'M' then 7
when type = 'v' and sex = 'M' then 8
when type = 'j' and sex = 'M' then 9
when type = 'i' and sex = 'H' then 10
when type = 'a' and sex = 'H' then 11
when type = 'c' and sex = 'H' then 12
when type = 'v' and sex = 'H' then 13
when type = 'j' and sex = 'H' then 14
end) asc, name asc;但是这段代码不能正常工作。这是我得到的结果：
| a | F |
| a | F |
| a | M |
| v | M |
| j | M |有没有什么办法让查询更清晰？
回答 2
Stack Overflow用户
发布于 2017-11-22 01:31:27
这可以使用Radim提到的order by case来实现：
order by (case when type = 'b' and sex = 'M' then 1
when type = 'b' and sex = 'F' then 2
when type = 'p' and sex = 'M' then 3
end) asc,name asc;这只是举个例子，你可以在when子句中进一步添加剩余的条件。
Stack Overflow用户
发布于 2017-11-22 01:49:48
select * from t
order by case when type = 'b' then 1
when type = 'p' then 2
when type = 'j' then 3
when type = 'i' then 4
when type = 'a' then 5
when type = 'c' then 6
when type = 'v' then 7
end,
case when sex = 'M' then 1
when sex = 'F' then 2
end,
name;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47418899
复制相关文章
相似问题
腾讯云开发者
