MySQL 查询以显示当前日期位于顶部的记录，并按降序显示其他记录 - MySQL query to show records with current date on top and others as per descending order

MySQL query to show records with current date on top and others as per descending order

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

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

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

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 子句 -

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

您可以将整个内容包装在另一个 SELECT 中并使用 ORDER BY

SELECT * FROM (     
     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
) AS all_sales
ORDER BY (sale_ends=CURDATE()) DESC, sale_ends DESC