sql - SQL - 选择数量相等的用户对
问题描述
我正在尝试编写一个返回具有相同贷款数量的用户对的查询。
我有这张桌子:
贷款
id_loan book_barcode id_user
1 123 1
2 321 2
3 456 3
4 678 4
5 721 1
6 934 2
这是我如何获得成对用户的代码:
SELECT l1.id_user user_1, l2.id_user user_2
FROM loans l1
JOIN loans l2 ON l2.id_user > l2.id_user
GROUP BY l2.id_user, l2.id_user;
这就是我想从查询中得到的,但我不知道如何以正确的方式比较两个计数,我试过但没有奏效。
id_user1 id_user2 number_loan
1 2 2
3 4 1
解决方案
最合理的方法是在加入前汇总贷款:
with u as (
select id_user, count(*) as num_loans
from loans l
group by id_user
)
select u1.id_user, u2.id_user
from u u1 join
u u2
on u1.num_loans = u2.num_loans and u1.id_user < u2.id_user;
您可以在不预先聚合的情况下执行此操作,但查询会更加昂贵:
select u1.id_user, u2.id_user
from loans u1 join
loans u2
on u1.id_user < u2.id_user
group by u1.id_user, u2.id_user
having count(distinct u1.id_loan) = count(distinct u2.id_loan);
我不推荐这种方法。