我正在尝试制作一个图表,其中X轴是日期(最近14天(,图表本身显示该日期的帖子计数。但是,它以1开头。oct->9.oct,然后进行到25。sep-> 9月30日
我的SQL:
SELECT DATE_FORMAT(created_at, "%d. %b") AS date, count(*) as count
FROM posts
WHERE created_at BETWEEN NOW() - INTERVAL 14 DAY AND NOW()
Group by date
ORDER BY date ASC
问题图片:https://i.stack.imgur.com/SPIgr.png
问题是order by
子句中的date
引用了select
子句中定义的别名,即日期的字符串表示。你不能用它对结果集进行你想要的排序。
这里有一个解决方法:向具有date
数据类型的group by
子句添加另一个表达式,然后可以使用它对结果进行排序:
SELECT date_format(created_at, '%d. %b') AS date, count(*) as count
FROM posts
WHERE created_at BETWEEN NOW() - INTERVAL 14 DAY AND NOW()
Group by date, date(created_at)
ORDER BY date(created_at) ASC