MySQL ORDER BY使用日期数据行 [英] Mysql ORDER BY using date data row

问题描述

我有一个类似这样的查询:

I have a query something like this:

SELECT 
 title, desc, date 
FROM 
 tablename 
ORDER BY 
 date ASC, title ASC;

当数据实际具有日期时，效果很好.问题是，日期提交是可选的，因此有时我会以注册日期作为日期，但不幸的是，将所有未日期的行放在顶部.

Works fine when the data actually has a date. Issue is, date submission is optional, so I sometimes get 0000-00-00 as a date, which has the unfortunate effect of placing all nondated rows on top.

所以，我然后尝试了这个:

So, I then tried this:

SELECT 
 title, desc, date 
FROM 
 tablename 
ORDER BY 
 date DESC, title ASC;

哪种作品，但不是真正的作品-所有带有日期(非注册日期)的项目均按降序排列，然后是带有注册日期的所有项目.

Which sort of works, but not really -- all items with dates (non 0000-00-00) get listed in descending order, followed by all items with 0000-00-00.

我想做的是按日期ASC排序，标题为ASC，但仅当日期！= 0000-00-00，但如果日期为= 0000-00-00，则只需在这些订单上按ORDER BY标题ASC(我想我解释正确了.)

What I want to do is order by date ASC, title ASC, but only if the date != 0000-00-00, but if date is = 0000-00-00, then just ORDER BY title ASC on those (I think I explained that correctly).

我能想到的唯一方法是基于非SQL的(两个查询，或者每个查询仅填充一个内存数组，然后使用PHP进行排序).

The only ways I can think to do this are non-SQL based (either 2 queries, or, each query just populates an in-memory array, and then I sort using PHP).

是否存在可以执行此操作的SQL查询?

Is there a SQL query that can do this?

推荐答案

您的2个查询解决方案是一个很好的解决方案，您可以使用

Your 2 query solution is a good one, you can do it all in SQL using the UNION command.

第一个查询将查询非零日期，然后查询UNION则查询零.

The first query will be for dates that are non-zero, then UNION in the query for dates that are zero.

类似

SELECT * FROM tbl 
   WHERE DATE != '0000-00-00' 
   ORDER BY date ASC 
UNION SELECT * FROM tbl
   WHERE DATE = '0000-00-00'
   ORDER BY title ASC

在这种情况下这可能不是很有用，但是对于复杂的查询，UNION可能会派上用场.

This may not be very useful in this instance, but for complex queries, UNION can come in handy.

这篇关于MySQL ORDER BY使用日期数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！