两张表关联查询，并统计其中一张表在关联条件下的数据个数

业务场景为统计项目表和用户表关联查询，项目id为用户的关联字段，一个项目下有多个项目，目标是查出来项目id，产品编号（项目里的字段）和某个项目id下的用户总数，过滤调剂是过滤掉过期的数据和被禁用及被删除的记录。

select p.id as projectId, p.product_code as productCode, count(u.project_id) as userNum from project_info p left join user_info u on p.id = u.project_id WHERE p.status = '1' and p.delete_flag = '0' and (p.valid_time < CURRENT_DATE or p.valid_time is null) and u.status = '1' group by p.id order by userNum desc

核心语句

select p.id as projectId, p.product_code as productCode, count(u.project_id) as userNum from project_info p left join user_info u on p.id = u.project_id group by p.id