使用类似
select accounts.id,
count(distinct s1.id) as num_req,
count(distinct s2.id) as num_grab
from accounts left join subs as s1 on accounts.id = s1.requester_account_id
left join subs as s2 accounts.id = s2.grabber_account_id
group by accounts.id
诀窍是使用表subs
两次:subs as s1
和subs as s2
,每次由不同的字段联接。
关于效率的注意事项:我不确定,但是我相信这个解决方案比子查询解决方案要快,尽管没有对其进行测试(至少不会慢一些)。left join
只要有可能,我总是更喜欢子查询。