一、mysql排序
我们在工作需要经常做一些,mysql的排序。我在这将mysql排序做一个小案例,将一部分mysql的排序情况做一个总结。如果不能找到下面对应的排序情况,可以看一下下面(5),(6),(7)排序,提供写出更加复杂情况的排序的思路。
SELECT * FROM `sys_user`
(1) mysql排序将null认为是最小值故,直接正序排序,将null会排在最前面位置
(因为user_code是varchar类型故是按照字符串的方式排序的)
select * from sys_user order by user_code
(2) 排序需要将正序时候需要将null排在最后面
select * from sys_user order by user_code is null,user_code asc
select * from sys_user order by user_code is not null desc,user_code asc
(3) 排序需要将倒叙序时候需要将null排在最前面
select * from sys_user order by user_code is null desc,user_code desc
select * from sys_user order by user_code is not null asc,user_code desc
(4) 排序将字符串转化数字按数字进行排序(正序)
select * from sys_user order by CONVERT(user_code,decimal(5,2)) asc
(5)排序将字符串转化数字按数字进行排序(正序),并将null排在最后
select * from sys_user order by user_code is null,CONVERT(user_code,decimal(5,2)) asc
select * from sys_user order by case when user_code is null then 999 else CONVERT(user_code,decimal(5,2)) end asc,user_code asc
(6)排序将字符串转化数字按数字进行排序(倒序),并将null排在最前面
select * from sys_user order by user_code is null desc,CONVERT(user_code,decimal(5,2)) desc
select * from sys_user order by case when user_code is null then 999 else CONVERT(user_code,decimal(5,2)) end desc,user_code desc
(7)根据user_state 为1 排在最前面,2排在中间,0排在最后,再根据user_code按数字排序,null放在最后
select
*, ( case when user_state = '1' then '0' when user_state = '2' then '1' else '2' end) AS state
from sys_user
order by state asc,user_code is null, CONVERT(user_code,decimal(5,2)) asc