基于空值获得排序SQL查询的最佳方法是什么

Question

CREATE TABLE `test` (
  `field1` int(11) NOT NULL,
  `field2` int(11) DEFAULT NULL,
  `field3` int(11) DEFAULT NULL,
  UNIQUE KEY `field1_2` (`field1`,`field2`),
  UNIQUE KEY `field1_3` (`field1`,`field3`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

null

• field2和field3为空的所有字段
• 所有field2为空、field3为值的字段
• 所有field3为空、field2为值的字段

到目前为止，我已经使用了三个与UNION连接的查询

SELECT * FROM test WHERE field2 IS NULL 
    AND field3 IS NULL UNION
SELECT * FROM test WHERE field2 IS NULL 
    AND field3 IS NOT NULL UNION 
SELECT * FROM test WHERE field2 IS NOT NULL 
    AND field3 IS NULL;

我自己觉得这是很多必要的代码来得到它的秩序，并希望有一个更好的解决方案。

那么有没有更好的办法呢？

Answer 1

只需将条件按顺序:

order by (field2 is null and field3 is null) desc,
         (field2 is null and field3 is not null) desc,
         (field2 is null and field3 is not null) desc

您可能需要添加，其中:

where field2 is null or field3 is null

这是因为MySQL将布尔值视为数字上下文中的数字，“true”为1，“false”为0。这就是为什么需要desc。在标准SQL中，您可以将相同的内容表示为：

order by (case when field2 is null and field3 is null then 1
               when field2 is null and field3 is not null then 2
               when field2 is not null and field3 is null then 3
               else 4
          end)