我有两个名为的表
- 用户
- 消息
用户表结构为,
id | mobile_no | created_atupdated_at||
---|---|---|---|
1 | 9876543210 | 2020-12-30 | |
2 | 9876543211 | 2020-12-30 | 2020-22-30 |
您需要group by
,如下所示:
select u.id, u.mobile_no, count(*) as msgs
from users u join messages m
on m.user_id = u.id
group by u.id, u.mobile_no
试试这个
select
user.id,
user.mobile_no,
count(*) as nos
from
users user
join messages message on message.user_id = user.id
group by
user.id,
user.mobile_no