首先按null排序，然后按其他变量排序 [英] Order by null first, then order by other variable

问题描述

这是我现在的代码:

SELECT id, number
FROM Media
WHERE user = 10
ORDER BY id, number

但是我希望它看起来像:

but I want it to look like:

SELECT id, number
FROM Media
WHERE user = 10
ORDER BY while(number IS NULL), id

我想做的是在结果的顶部放置所有number都是NULL，但是一旦number不是NULL，请按id

What I want to do is to have all number that are NULL on the top of the result, but as soon number is not NULL, sort by id

有可能吗?

我使用mysql.

推荐答案

类似这样的内容:

SELECT id, number
FROM Media
WHERE user = 10
ORDER BY (case when number is null then 0 else 1 end), id

如果number为NULL，则按条件的第一顺序为0；否则为0.否则1
这意味着每行将为NULL的行将排在其他行之前

If number is NULL, the first order by criteria will be 0 ; else 1
Which means every line will number NULL will come before the others ones

请注意，无论如何，id也会被排序.

And note that ids will be sorted too, anyway.

您会得到这样的东西:

• number null; id = 1
• number null; id = 2
• number null; id = 5
• number null; id = 8
• number不为null； id = 3
• number不为null； id = 4
• number不为null； id = 7
• number不为null； id = 10
• number不为null； id = 12

这篇关于首先按null排序，然后按其他变量排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！