SQL-orderby将null的记录置后/置前
如何使null值的记录排序到最后?
在使用orderby进行查询时,有时候会遇上null值,那排序默认就会置前(mysql置前,oracle置后),那如何调整null值记录的排序?
1. 设置排序
- order by colum asc 时,null默认被放在最前
- order by colum desc 时,null默认被放在最后
# null被强制放在最前,不为null的按声明顺序[asc|desc]进行排序
SELECT
`name`,
order_no
FROM
tb_product_category
ORDER BY
IF
( ISNULL( order_no ), 0, 1 ),
order_no DESC
# null被强制放在最后,不为null的按声明顺序[asc|desc]进行排序
SELECT
`name`,
order_no
FROM
tb_product_category
ORDER BY
IF
( ISNULL( order_no ), 1, 0 ),
order_no DESC
2. 使用COALESCE
使用数据库DBMS内置的COALESCE方法,
COALESCE(列名,排序值) 当指定的列名为null时,将设置排序值
如下示例
SELECT
`name`,
COALESCE ( order_no, 999999 ) AS order_no2
FROM
tb_product_category
ORDER BY
order_no2
3. 使用case语句
使用case语句,当order_no为null时,则设置指定排序值
SELECT
`name`,
CASE
WHEN order_no IS NULL THEN
999999 ELSE order_no
END AS order_no2
FROM
tb_product_category
ORDER BY
order_no2