mysql 报告 : count unique login per day

Question

我有(例如)一个包含以下内容的 mysql 表“登录”:

user_id | last_login
1       | 2015-02-01 05:01:07
1       | 2015-02-01 12:42:09
2       | 2015-02-01 22:16:23
2       | 2015-02-02 15:45:23
2       | 2015-02-04 21:27:04
3       | 2015-02-04 06:25:45
4       | 2015-02-05 03:12:01

我的问题是:如何生成每天所有唯一身份用户的摘要。所以我会这样报告:

day        | count
2015-02-01 | 2
2015-02-02 | 1
2015-02-04 | 2
2014-02-05 | 1

此查询无效:

SELECT DATE_FORMAT( login_date, '%Y-%m-%d' ) AS `day`
(SELECT COUNT(DISTINCT(user_id)) AS count, DATE_FORMAT( login_date, '%Y-%m-%d' ) AS `sdate` WHERE `sdate` = day )
FROM `logins`
GROUP BY `day`
ORDER BY `day` // query not working.....

如何获取每天所有唯一用户的 SQL 摘要...？

Answer 1

您可以在“group by”和“order by”子句中使用别名。此查询将为您提供报告:

select DATE_FORMAT(`last_login`, '%Y-%m-%d') as theDate, 
count(distinct(user_id)) as theCount from logins 
group by theDate 
order by theCount;

希望这对您有所帮助。