order by排序如何才能按照先数字从小到大，再把为空的数字字段排在最后？

库里面有个排序字段，数字型的，老板希望对填写次序的按小到大，没有填写次序的就按ID号从大到小，但是desc和asc不是只能从升到降或从降到升吗，空白的次序可是比１都大啊，这样就排到前面去了，如果按降序，那么次序大的反倒排前面去了，有没有什么办法

15 个解决方案

#1

select * from test where code is not null order by code union select * from test where code is null

#2

我用这种方式，为什么空的还是显示在最上？有次序的都排到最下去了

#3

分开2次查询分开2次输出不就行了

#4

(select 1 as tbid, * from test where code is not null order by code) union (select 2 as tbid, * from test where code is null order by code) order by tbid

#5

tbid 为自己加的字段，你随便取什么名字都可以，这个只是为了后面order by 的时候，用来排序的时候用的。

#6

SELECT *, IF(ISNULL(tbid),9999999999999999999,tbid) AS new_order FROM tb ORDER BY new_order

#7

还是不行啊，和普通查询那种一样，空的在上，有顺序的在下

#8

至少我测试是OK的。

#9

如果楼上各位的都不能解决你的问题的话那考虑一下存储过程里用临时表解决。

#10

(select 1 as tbid, * from test where code is not null order by code) union (select 2 as tbid, * from test where code is null order by code) order by tbid desc,id

#11

(select 1 as tbid, * from test where code is not null order by code) union (select 2 as tbid, * from test where code is null order by code) order by tbid

我写的这个不行，是不可能的。搂主你看懂了没？tbid是实际不存在的字段。放在这里就是为了排序用的。

#12

(SELECT 1 AS tbid, *
FROM ceshi
WHERE flag IS NOT NULL)
UNION
(SELECT 2 AS tbid, *
FROM ceshi
WHERE flag IS NULL)
ORDER BY tbid, id DESC

#13

调试过了。...

#14

SELECT *,ISNULL(flag,0) AS new_order FROM ceshi ORDER BY new_order desc

#15

我把id DESC去掉了才算行了

#1