我有一个users
表
id | username | password | created_at
---------------------------------------------------
1 | foo | p4ssw0rd | 2016-01-01 12:00:00
2 | bar | 123456 | 2016-01-02 12:00:00
....
我如何实现这个结果,只有一年周的投入(例如:'201605'
,2016 年第 5 周)
total_users | date
------------------------
4 | 2016-01-04
6 | 2016-01-05
7 | 2016-01-06
10 | 2016-01-07
12 | 2016-01-08
12 | 2016-01-09
15 | 2016-01-10
如您所见,它显示每周的total_users
,每天一行。
我知道这要求太多了,如果你愿意帮助我,我会提前感谢你。
使用 MySQL
WEEK 函数 。MySQL WEEK()
返回给定日期的周数
SELECT count(*) total_users,date(created_at) date
FROM `table`
where concat(YEAR(created_at),WEEK(created_at))='201605'
group by date(created_at)
SELECT
COUNT(user) as Total_users,
date(created_at) as Date
FROM Users
GROUP BY date(created_at);
问候