查找具有两列的唯一组合的所有行 [英] Finding all rows with unique combination of two columns

问题描述

我有此表封邮件;

sender_id    recipient_id
1            2
1            3
1            3
2            1
3            1
2            3

我希望选择以下行：

1. 要么 sender_id 或 receiver_id = current_user.id 。


2. 另一个字段应该是唯一的。

1. Either sender_id or receiver_id = current_user.id.
2. The other field should be unique.

Ie我想从其中 sender_id = 2 或 recipient_id = 2 的表中选择唯一，我需要以下结果：

I.e. I want to select unique from table where sender_id = 2 or recipient_id = 2 and I need this result:

sender_id    recipient_id
2            1
2            3

该怎么做？

为什么？因为我希望构建一个类似于Facebook的收件箱，在该收件箱中，已发送和已接收的消息将被汇总，而该查询是迄今为止的瓶颈。

How to do it?
Why? Because I wish to build a facebook-like inbox in which sent and received messages are aggregated, and this query is the bottleneck so far.

我正在使用Rails 3.2和Postgres 9.3。

I am using rails 3.2 and Postgres 9.3.

推荐答案

SELECT sender_id AS user_id, recipient_id AS other_user_id
FROM   messages
WHERE  sender_id = $current_user_id

UNION
SELECT recipient_id, sender_id
FROM   messages
WHERE  recipient_id = $current_user_id
-- ORDER BY 1, 2  -- optional

UNION （不是 UNION ALL ）从结果中删除重复项，从而使 DISTINCT 变得不必要。
您可能想在末尾添加 ORDER BY 以获得排序的输出。

UNION (not UNION ALL) removes duplicates from the result makingDISTINCT unnecessary. You might want to add ORDER BY at the end for sorted output.

假设一个大表由于合格行相对较少，因此两个btree索引通常可提供最佳性能。
$，其中一个具有领先者或仅 sender_id ，另一个具有领先者或仅 sender_id 。

Assuming a big table with relatively few qualifying rows, two btree indexes typically deliver best performance. One with leading or only sender_id, another one with leading or only recipient_id.

单个 多列索引 在（sender_id，receiver_id）上也可以，但是反之亦然。请参阅：

A single multicolumn index on (sender_id, receiver_id) or vice versa also works, but typically slower. See:

• 是复合索引也适合查询第一个字段？

• Is a composite index also good for queries on the first field?

这篇关于查找具有两列的唯一组合的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！