SQL 编写 排序时如何将NULL排在最后面
下面先直接排序看下效果
-
select UserInfoID,User_No,User_Names -
from UserInfo -
order by User_NO asc
可以看到指定排序的列，其值为 null 的排在了最前面。
下面就是解决办法。
-
select UserInfoID,User_No,User_Names -
from UserInfo -
order by case when User_NO is null then 1 else 0 end asc,User_NO asc
本人sql
select
upper(a.machine_sn) as "machineSn",
b.machine_type as "machineType",
c.project_name as "projectName",
get_affiliaction_name(a.class_id, a.department_id) as "affiliationName",
a.project_id as "projectId",
a.class_id as "classId",
a.department_id as "departmentId",
d.send_time as "sendTime",
a.usage_begin as "usageBegin",
a.usage_end as "usageEnd",
a.machine_status as "machineStatus",
a.notes as "notes",
a.modify_time as "modifyTime"
from project_machine as a
left join machine_profile as b on a.machine_sn = b.machine_sn
left join project_profile as c on a.project_id = c.project_id
left join material_logistics as d on a.machine_sn = d.material_sn
and a.project_id = d.receive_project_id
and a.class_id = d.receive_class_id
and a.department_id = d.receive_department_id
WHERE a.history_record = 1
order by case when a.modify_time is null then 0 else 1 end desc,a.modify_time desc
