mysql - 查询差异

Question

我有两个表:

• table1:id，已创建

• 表2:id、modi

+------------+----------+
|    id      | created  |
+------------+----------+
|    1       | 18/01/01 |
|    2       | 18/01/01 |
|    3       | 18/01/01 |
|    4       | 18/01/01 |
+------------+----------+  

+------------+----------+
|    id      | modi     |
+------------+----------+
|    1       | 18/01/02 |
|    4       | 18/01/02 |
|    1       | 18/01/03 |
|    2       | 18/01/03 |
|    3       | 18/01/04 |
|    2       | 18/01/04 |
|    2       | 18/01/05 |
+------------+----------+   

我需要一个查询(MySQL)来打印每个用户修改日志所需的天数(按天分组)。例如，1 天 - 3 个用户，2 天 - 7 个用户，等等...

+------------+----------+
|    days    | num_id   |
+------------+----------+
|    1       | 2        |
|    2       | 1        |
|    3       | 1        |
+------------+----------+

我设法做到了这一点:

select datediff(table1.id, table2.modi) as date_diff, count(*) as nums
from table1, table2
where table1.id = table2.id
group by date_diff

问题是，除了第一个条目之外，它还包括第二个条目(或更多)，但我只想包含第一个条目。

谢谢。

Answer 1

您需要为 datediff 功能使用正确的列

select datediff(table3.modi,table1.created) as date_diff, count(*) as nums
    from table1 left  join 
    (
    select id,min(modi) as modi  from table2 group by id
    ) 
    as table3
    on table1.id = table3.id
    group by date_diff