php - 关于查新增用户的sql

Question

有三张表 用户表：users(id), 圈子表：circle(id), user_circle(id, circle_id, user_id)
现在要查询所有圈子每个圈子的每日新增用户数，用一个sql怎么查？

如果查一个圈子的每日新增可以用如下方式查到：其中userCircles是关联模型，可以得到circle对应在user_circle里的所有记录
/**

 * 每日新增统计
 */
public function getDailyIncrement()
{
    return self::getDailyStatisticQuery($this->userCircles());
}

 public static function getDailyStatisticQuery($query)
{
    return $query->select(DB::raw("circle_id, DATE_FORMAT(created_at,'%Y-%m-%d') as time, count(*) as count"))
        ->groupBy('time')
        ->get();
}

Answer 1

已解决：

select circle_id, DATE_FORMAT(uc.created_at,'%Y-%m-%d') as time, count(*) as count 
from user_circle as uc LEFT JOIN 
circle ON uc.circle_id = circle.id 
group by `time` ORDER BY uc.circle_id;

Answer 2

select tmp.circle_id,tmp.created_at,count(1) as count from 
(select uc.*,u.name,c.title from user_circle as uc 
left join circle as c on uc.circle_id = c.id 
left join users as u on uc.user_id = u.id 
where created_at > CURDATE()) as tmp
group by tmp.circle_id
having created_at > CURDATE()
order by tmp.circle_id asc

将全部大于今天0点的数据全部查出，group by 分组

=================================================
users表结构(id,name),
circle结构(id,title),
user_circle结构(id,circle_id,user_id，created_at)
created_at类型为date,0000-00-00 00:00:00
可以正确查询出大于今天00:00:00时间记录
先将今天的数据全部查出，存储在临时表中作为子查询，然后count(1)计数，group by 分组

不需要联合查询即可实现，上面查的的今天的结果

select *,count(1) as count from user_circle
group by created_at
order by circle_id asc