php - 在 SELECT 中格式化 curdate()

Question

我使用以下方法计算过去 10 天的新用户数:

SELECT days.day, count(u.user_id)
FROM
(select curdate() as day
union select curdate() - interval 1 day
union select curdate() - interval 2 day
union select curdate() - interval 3 day
union select curdate() - interval 4 day
union select curdate() - interval 5 day
union select curdate() - interval 6 day
union select curdate() - interval 7 day
union select curdate() - interval 8 day
union select curdate() - interval 9 day) days
left join users u
on days.day = DATE(u.dateadded)
group by
days.day

它完美地工作但给出了 date_format，因为它应该是 2014-03-18 - 理想情况下，我希望日期格式是例如“3 月 18 日星期二”

这就是我正在尝试的，但只返回 2 行，它们在天数列下显示 null 和 BLOB

SELECT days.day, count(u.user_id)
FROM
(select DATE_FORMAT(curdate(), '%a %D %b') as day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 1 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 2 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 3 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 4 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 5 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 6 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 7 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 8 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 9 day) days
left join users u
on days.day = DATE_FORMAT(u.dateadded, '%a %D %b')
group by
days.day

有更好的方法吗？

我正在使用 MySQL 和 PHP

Answer 1

当然，JOIN 必须保持不变。无需在那里使用 DATE_FORMAT()。

SELECT DATE_FORMAT(days.day, '%a %D %b') AS "day", count(u.user_id)
FROM
(select curdate() as day
union select curdate() - interval 1 day
union select curdate() - interval 2 day
union select curdate() - interval 3 day
union select curdate() - interval 4 day
union select curdate() - interval 5 day
union select curdate() - interval 6 day
union select curdate() - interval 7 day
union select curdate() - interval 8 day
union select curdate() - interval 9 day) days
left join users u
on days.day = DATE(u.dateadded)
group by
days.day