按`updated_at`和`created_at`排序，即使它们为NULL [英] Order by `updated_at` and `created_at` even if they are NULL

问题描述

我有下表:

CREATE TABLE `entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

我想同时按updated_at和created_at对条目进行排序.

I want to sort the entries by both updated_at and created_at.

但是其中任何一个都可能是NULL.

But either of them could be NULL.

我的查询:

SELECT *
FROM `entries`
ORDER BY `updated_at` DESC, `created_at` DESC, `id` DESC

但这会将所有未更新的条目放在结果的底部.

But this will put every entry which has not been updated at the bottom of the results.

基本上，我需要按可用的最后日期对其进行排序.如果没有可用的id排序.

Basically I need them to be sorted by the last date available. And if there is none available to be sorted by id.

推荐答案

我相信您想要的是:

ORDER BY coalesce(updated_at, created_at) DESC, id DESC

此命令按更新日期(如果可用)或创建日期(如果不可用)排序.然后按ID降序排列.

This orders by either the updated date (if available) or the created date (if it is not available). Then it orders by id descending.

这篇关于按`updated_at`和`created_at`排序，即使它们为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！