从多个自身左连接中删除重复项 [英] Removing duplicates from multiple self left joins

问题描述

我动态地生成如下所示的查询，通过左侧加入（任何次数）对自身创建不同的规则组合，并避免与连接条件的一部分具有一些相同属性的规则，例如。

  SELECT count（*）
 FROM rules AS t1 
 LEFT JOIN规则AS t2 
 ON t1.id！= t2.id 
 AND ... 
 LEFT JOIN规则AS t3 
 ON t1.id！= t2.id AND t1.id！= t3.id AND t2 .id！= t3.id 
 AND ... 
  

我正在删除重复项从连接的行创建一个ids数组，然后排序和分组：

  SELECT sort（array [t1.id，t2 .id，t3.id]）AS ids 
 ... 
 GROUP BY ids 
  

我想知道是否有更好的方式删除重复的行，例如

  t1.ID | t2.ID | t3.ID 
 --------------------- 
 A | B | C 
 C | B | A 
  

应该是

  t1.ID | t2.ID | t3.ID 
 --------------------- 
 A | B | C 
  

或

 code> t1.ID | t2.ID | t3.ID 
 --------------------- 
 C | B | A 
  

但不能同时使用。

编辑：我想从行排列到组合行。

解决方案

我建议不要加入！ =，尝试加入< =。

然后，您将具有t1.id> t2.id，t2.id> t3.id等所有组合。

行不会是重复，因为它们是有序集，并且包含等效成员的任何集合必然会导致相同的有序集。

I am dynamically generating a query like below that creates different combinations of rules by left joining (any number of times) on itself and avoiding rules with some of the same attributes as part of the joins conditions e.g.

SELECT count(*) 
FROM rules AS t1 
LEFT JOIN rules AS t2
 ON t1.id != t2.id
 AND ...
LEFT JOIN rules AS t3
 ON t1.id != t2.id AND t1.id != t3.id AND t2.id != t3.id
 AND ...

I am currently removing duplicates by creating an array of ids from the joined rows then sorting and grouping by them:

SELECT sort(array[t1.id, t2.id, t3.id]) AS ids
...
GROUP BY ids

I would like to know if there is a better way of removing duplicate rows e.g.

t1.ID | t2.ID | t3.ID
---------------------
  A   |   B   |   C
  C   |   B   |   A

Should be

t1.ID | t2.ID | t3.ID
---------------------
  A   |   B   |   C

Or

t1.ID | t2.ID | t3.ID
---------------------
  C   |   B   |   A

But not both.

EDIT: I would like to go from a permutation of rows to a combination rows.

解决方案

I'd suggest rather than joining on !=, try joining on <=.

You will then have all combinations with t1.id > t2.id, t2.id > t3.id, and so on.

Rows will not be 'duplicates' because they are ordered sets, and any set containing equivalent members would necessarily result in the identical ordered set.

这篇关于从多个自身左连接中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！