如果Column1不为null，则按Column1排序，否则按Column2排序

Is there a way to combine ORDER BY and IS NULL in sql so that I can order by a column if the column isn't null, but if it is null, order by another column?

有没有办法在sql中组合ORDER BY和IS NULL，这样如果列不为null，我可以按列排序，但如果它是null，则按另一列排序？

Thanks!

谢谢！

4 个解决方案

#1

Something like:

就像是：

ORDER BY CASE 
    WHEN Column1 IS NOT NULL THEN Column1
    ELSE Column2
END

Same as writing:

与写作相同：

ORDER BY IFNULL(Column1, Column2)

#2

Try this

尝试这个

  ORDER BY COALESCE(fieldA, fieldB);

#3

You could try with the following:

您可以尝试以下方法：

ORDER BY ISNULL(firstField, secondField)

#4

I dont have any Tables atm where I could test it, but this may work, at least it did without useable data:

我没有任何Table atm我可以测试它，但这可能有用，至少它没有可用的数据：

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id 
WHERE 1 
ORDER BY IF( table2.id, table1.id, table1.name )

Also I don't know how the order would look like if table2.id is null sometimes, seems very instable.

另外，我不知道如果table2.id有时为空，那么顺序会是什么样子，看起来非常不稳定。

#1