mysql 或 postgres 中两列的唯一组合 [英] Unique combination of two columns in mysql or postgres

问题描述

我必须获得两列的独特组合.

I have to get unique combinations of two columns.

例如，如果值是:

sender_id   recipient_id   created_at

    1            2         10/11/2014
    2            1         10/12/2014 
    1            2         10/13/2014
    1            3         10/14/2014

我希望输出为:

sender_id   recipient_id   created_at

    1            3         10/14/2014
    1            2         10/13/2014

我写了这个查询:

SELECT DISTINCT ON (sender_id, recipient_id) *
FROM "messages"
WHERE ((recipient_id = 1 and recipient_delete = false) 
    or (sender_id = 1 and sender_delete = false))
ORDER BY sender_id, recipient_id, created_at DESC

但它输出这个:

sender_id   recipient_id   created_at

    1            3         10/14/2014
    2            1         10/12/2014 
    1            2         10/13/2014

推荐答案

获取所有对的一个选项，无论它们是向前还是向后(例如 (1, 2) == (2, 1))是从每一行中选择 LEAST() 和 GREATEST()，然后选择不同的值.使用此查询:

One option to get all pairs, regardless of whether they are forward or backward (for example (1, 2) == (2, 1)) is to select the LEAST() and GREATEST() from each row, and then select distinct values. Using this query:

SELECT DISTINCT LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id)
FROM myTable;

您将获得以下输出:

| 1 | 2 |
| 1 | 3 |

一旦你有了它，你就可以按这些分组以获得每对的最大日期:

Once you have that, you can GROUP by these to get the maximum date for each pair:

SELECT LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id), MAX(created_at)
FROM myTable
GROUP BY LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id);

此查询将为您提供每对所需的数据，但不会返回原始表中的实际行.如果有一行格式 |2 |1 |2014-10-15 | 此查询将返回 |1 |2 |2014-10-15.

This query will give you the data you need for each pair, but it won't return the actual row from your original table. If there is a row of format | 2 | 1 | 2014-10-15 | this query will return | 1 | 2 | 2014-10-15.

要从表中获取原始行，您需要JOIN，条件是所有必要的列都匹配:

To get the original row from your table, you need to JOIN on the condition that all of the necessary columns match:

SELECT m.*
FROM myTable m
JOIN(
  SELECT LEAST(sender_id, recipient_id) AS least, 
    GREATEST(sender_id, recipient_id) AS greatest,
    MAX(created_at) AS maxDate
  FROM myTable
  GROUP BY LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id)) tmp
ON tmp.least = LEAST(m.sender_id, m.recipient_id) AND tmp.greatest = GREATEST(m.sender_id, m.recipient_id) AND tmp.maxDate = m.created_at;

这是一个符合您预期结果的 SQL Fiddle 示例.

Here is an SQL Fiddle example that matches your expected results.

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