php - mysql - 选择对多个表进行计数、按周分组、自定义日期范围

Question

例如，我有 2 个表和一个日期范围(2015 年 12 月 1 日 - 2016 年 1 月 10 日)。

第一个表:USERS

id (int)   date (datetime)
 1      3-dec-2015
 2      4-dec-2015
 3      19-dec-2015
 4      20-dec-2015
 5      21-dec-2015
 6      29-dec-2015
 7      30-dec-2015

第二个表:生日

id (int)   date (datetime)
 1      6-dec-2015
 2      8-dec-2015
 3      9-dec-2015
 4      17-dec-2015
 5      28-dec-2015

查询后的结果应如下:

[0] 1st week => 2 users, 1 birthday
[1] 2nd week => 0 users, 2 birthday
[2] 3ed week => 1 users, 1 birthday
[3] 4th week => 1 users, 0 birthday
[4] 5th week => 2 users, 1 birthday
[5] 6th week => 0 users, 0 birthday

有什么想法可以实现这个结果或接近的结果吗？如果需要的话我可以使用 PHP。

Answer 1

我会从这样的事情开始:

select ((week(dateb) - week('2015-12-01')) + 1) as week_number, count(a.dateb) as userdates 
from users as a
where dateb between '2015-12-01' and '2016-01-01'
group by week(dateb)
order by week(dateb);

和

select ((week(dateb2) - week('2015-12-01')) + 1) as week_number, count(dateb2) as birthdays 
from birthdays
where dateb2 between '2015-12-01' and '2016-01-01'
group by week(dateb2)
order by week(dateb2);

演示，http://sqlfiddle.com/#!9/c83cb/21

从那里你可以用 PHP 来调整输出。

另请注意，此方法仅返回包含填充数据的行。因此，您应该检查每行增加 1 的迭代。

例如因此，对于用户，当您从第 1 周到第 3 周时，您应该输出week 2 = 0；或者您想要显示它的任何方式。