当天排在最前面可以按照
select * from table ORDER BY CASE cdate WHEN current_date() THEN 0 ELSE 1 END
但这个只能是将当天的排在前面,后面的日期是随机的,希望后面的日期能按序排列
谢谢
...全文
6076打赏收藏
查询排序按照当天排在最前面,剩下的按照日期倒序排在后面
查询数据表,按照日期 cdate 进行排序, 当天的排在最前面,然后后面的按照日期倒序 比如 : 1 2020-11-12 2 2020-11-12 3 2020-12-10; 4 2020-11-28 5 2020-11-05 6 2020-10-10 当天排在最前面可以按照 select * from table ORDER BY CASE cdate WHEN current_date() THEN 0 ELSE 1 END 但这个只能是将当天的排在前面,后面的日期是随机的,希望后面的日期
select * from
(
SELECT t.* from test t where t.create_time > CURDATE() ORDER BY t.create_time DESC
)a
union all
SELECT * from (
SELECT t.* from test t where t.create_time < CURDATE() ORDER BY t.create_time asc
)b
select *,case datediff(cdate,current_date())
when 0 then date_add(cdate, INTERVAL -99999 DAY)
else cdate end as sort_date
from table ORDER BY sort_date
没那么麻烦,直接执行这个语句就可以了。
SELECT T.* FROM (
SELECT '2020-11-12' D FROM DUAL UNION ALL
SELECT '2020-11-12' D FROM DUAL UNION ALL
SELECT '2020-12-10' D FROM DUAL UNION ALL
SELECT '2020-11-28' D FROM DUAL UNION ALL
SELECT '2020-11-05' D FROM DUAL UNION ALL
SELECT '2020-10-10' D FROM DUAL) T
ORDER BY IF(D=DATE_FORMAT(CURRENT_DATE(), '%Y-%m-%d'),0,1) , D DESC ;