MySQL查询按照降序显示当前日期的记录，其他的显示记录 [英] MySQL query to show records with current date on top and others as per descending order

问题描述

我在我的数据库中使用以下查询，

I am using the following query in my database,

SELECT b.sales_id,b.category_id,b.sale_starts,b.sale_ends 
FROM tbl_sales b WHERE b.active=1 
UNION
SELECT b.sales_id,b.category_id,b.sale_starts,b.sale_ends 
FROM tbl_sales b INNER JOIN tb_category c ON  b.category_id=c.cat_id 
WHERE c.cat_keyword LIKE 'a' ORDER BY sale_ends  DESC

并得到如下结果，

sales_id  | category_id         |sale_starts | sale_ends 
----------|---------------------|------------|--------------
 1        |   10                | 2012-03-31 | 2012-04-30     
 2        |   11                | 2012-03-22 | 2012-04-27
 3        |   25                | 2012-03-31 | 2012-04-25
 4        |   12                | 2012-04-05 | 2012-04-11

现在我需要得到如下结果，即具有今天的日期/当前日期为 sale_ends必须显示在订单顶部（假设今天的日期/当前日期是 2012-04-11 ），如下所示 -

Now i need to get the result as follows, ie the row which has today's date/current date assale_ends must be shown in the top of the order (assuming today's date/current date is 2012-04-11), like shown below-

sales_id      | category_id         |sale_starts | sale_ends 
    ----------|---------------------|------------|--------------
     4        |   12                | 2012-04-05 | 2012-04-11 (today's date)
     1        |   10                | 2012-03-31 | 2012-04-30     
     2        |   11                | 2012-03-22 | 2012-04-27
     3        |   25                | 2012-03-31 | 2012-04-25

需要帮助，谢谢提前。

推荐答案

尝试这个ORDER BY子句与条件 -

Try this ORDER BY clause with condition -

ORDER BY IF(sale_ends = DATE(NOW()), 0, 1), sale_ends DESC

这篇关于MySQL查询按照降序显示当前日期的记录，其他的显示记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！