用不同的键将同一张桌子连接两次 [英] Join same table twice on different key

问题描述

我有两个表accounts和subs.
第一个表具有id，第二个表具有字段id，requester_account_id，grabber_account_id

I have a two tables accounts and subs.
The first table has id and the second table has fields id, requester_account_id, grabber_account_id

我想计算一个帐户进行了多少次子请求，以及他进行了多少次抓取(基于requester_account_id/grabber_account_id是否填充了他的id)

I want the count of how many sub requests an account made, and how many grabs he did (based on if the requester_account_id/grabber_account_id is populated with his id)

在一个查询中

输出:

+------------+----------------+-----------------+
| account_id | subs_requested | subs_grabbed    |
+------------+----------------+-----------------+
|  1         | 4              | 3               |
|  3         | 2              | 1               |
+------------+----------------+-----------------+

推荐答案

使用类似

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，每次都由不同的字段联接....

The trick is to use table subs twice: subs as s1 and subs as s2, every time joined by different field....

关于效率的注意事项:我不确定，但是我相信这个解决方案比子查询解决方案要快，尽管没有对其进行测试(至少不会慢一些).在可能的情况下，我总是更喜欢left join而不是子查询.

Note regarding efficiency: I'm not sure but I believe this solution is faster than the subquery solution, not tested it though (at least it won't be slower). I always prefer left join over subquery whenever possible.

这篇关于用不同的键将同一张桌子连接两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！