时间戳

首页 > 解决方案 > GROUPBY 多对多与自身

sql - GROUPBY 多对多与自身

问题描述

我有一个与自身具有多对多关系的用户表，我想获取所有具有这种特定关系的用户对。问题是，在关系表中，我这样存储用户：

+------+---------------+
| User |   relation    |
+------+---------------+
| id   | left_user_id  |
| name | right_user_id |
| ...  | ...           |
+------+---------------+

所以当我做一个基本的

SELECT count(*)
FROM relation LEFT OUTER JOIN user AS user_1 ON user_1.id = relation.left_user_id
              LEFT OUTER JOIN user AS user_2 ON user_2.id = relation.right_user_id 
GROUP BY left_user_id, right_user_id;

我有时会为同一对得到两个结果（例如，有时 (Adam, Eva) 和 (Eva, Adam) 是同一对）。我想要实现的只是一对：（亚当，伊娃）。

如何做到这一点？

标签： sqlpostgresqlmany-to-many

解决方案

您可以使用函数least()和greatest()：

SELECT count(*)
FROM relation r
LEFT OUTER JOIN user AS user_1 ON user_1.id = r.left_user_id
LEFT OUTER JOIN user AS user_2 ON user_2.id = r.right_user_id 
GROUP BY LEAST(r.left_user_id, r.right_user_id), GREATEST(r.left_user_id, r.right_user_id);

或者在这种不需要连接的情况下：

SELECT count(*)
FROM relation 
GROUP BY LEAST(left_user_id, right_user_id), GREATEST(left_user_id, right_user_id);

推荐阅读