mysql - MySQL中基于今天日期的项目计数

Question

我有 2 个表，一个有 user_info，另一个有 user_activies。

我有一个从 user_info 表中获取多行的查询。我想添加到这个查询中的是；

我想从 user_activities 表中获取今天用户事件并计数它们。

user_info
| id | name | views | lastlogin | regdate | group_id |

user_activities
| id | userid | activity | date |

当前查询

select id, name, views, lastlogin 
from user_info 
where group_id = 2 
ORDER BY user_info.id ASC

我如何连接今天完成的事件总数？

提前致谢!

Answer 1

我假设日期是日期类型:

select 
   u.id,
   u.name,
   u.views,
   u.lastlogin,
    sum(
  -- if datetime IF (date(date) = curdate() , 1, 0)
  IF (date = curdate() , 1, 0)
     ) as 'today_activities'

from user_info u 
      -- using left join you will get the list of all user even if they
      -- don't have any activities today with sum activities= 0
      LEFT JOIN user_activities a on u.id = a.userid
where 
   group_id = 2
group by u.id
ORDER BY u.id ASC