有两个表如下
phone_t
id name phone
0 a 110
1 aa 110
2 bb 110
3 aa 120
4 dd 119
call
id name phone
1 aa 110
2 aa 110
查询出如下结果
id name phone count
0 a 110 0
1 aa 110 2
2 bb 110 0
3 cc 120 0
4 dd 119 0
如何用一个sql语句实现
...全文
1087打赏收藏
求一个sql语句
有两个表如下 phone_t id name phone 0 a 110 1 aa 110 2 bb 110 3 aa 120 4 dd 119 call id name phone 1 aa 110 2 aa 110 查询出如下结果 id name phone count 0 a 110 0 1 aa 110 2 2 bb 110 0 3 cc 120 0 4 dd 119 0 如何用一个sql语句实现
select a.id,a.name,a.phone,case when b.count is null then 0 else b.count end from phone_t a
left outer join
(
select a.id,a.name ,a.phone,count(*) as count from phone_t a, call b, callphone c where a.name = b.name and a.phone = c.phone and b.id = c.callid
group by a.id,a.name ,a.phone
) b
on a.id = b.id and a.name = b.name and a.phone = b.phone
修改如下
有两个表如下
phone_t
id name phone
0 a 110
1 aa 110
2 bb 110
3 aa 120
4 dd 119
call
id name
1 aa
2 aa
callphone
callid phone
1 110
2 110
查询出如下结果
id name phone count
0 a 110 0
1 aa 110 2
2 bb 110 0
3 cc 120 0
4 dd 119 0
如何用一个sql语句实现