PostgreSQL - 选择不同（column1，column2）条件所在的地方 [英] PostgreSQL - Select distinct(column1, column2) where a condition holds

问题描述

我有以下表格和一些示例记录：

  id | attr1_id | attr2_id | user_id | rating_id | 
 ------ + ---------- + ---------- + ----------------- -  + ----------- + 
 1 | 188 | 201 | [e-mail] | 3 | 
 2 | 193 | 201 | [e-mail] | 2 | 
 3 | 193 | 201 | [e-mail] | 1 | 
 4 | 194 | 201 | [e-mail] | 1 | 
 5 | 194 | 201 | [e-mail] | 1 | 
 6 | 192 | 201 | [e-mail] | 1 | 
  

（ attr1_id ， attr2_id ， user_id ）是 UNIQUE ，意味着每个用户只能创建我的目标是选择（attr1_id，attr2_id）的所有不同组合，其中 rating_id = 1

/ code>，但只选择 attr1_id 和 attr2_id 的每个组合只有一次不存在具有 rating_id>的任何其他行（由其他用户） 1 并引用相同的 attr1_id 和 attr2_id 。
注意，可以切换 attr1_id 和 attr2_id 的组合，所以给定这两个记录： / p>

  id | attr1_id | attr2_id | user_id | rating_id | override_comment 
 ------ + ---------- + ---------- + ---------------- ---- + ----------- + ------------------ 
 20 | 5 | 2 | [e-mail] | 3 | 
 ------ + ---------- + ---------- + ----------------- --- + ----------- + ------------------ 
 21 | 2 | 5 | [e-mail] | 1 | 
  

不应计算行，因为行引用 attr_ids ，其中一个具有 rating_id> 1

但是，如果这两行存在：

  id | attr1_id | attr2_id | user_id | rating_id | override_comment 
 ------ + ---------- + ---------- + ---------------- ---- + ----------- + ------------------ 
 20 | 5 | 2 | [e-mail] | 1 | 
 ------ + ---------- + ---------- + ----------------- --- + ----------- + ------------------ 
 21 | 2 | 5 | [e-mail] | 1 | 
 ------ + ---------- + ---------- + ----------------- --- + ----------- + ------------------ 
 22 | 2 | 5 | [e-mail] | 1 | 
  

所有行只能计为一个，因为它们都共享 attr1_id 和 attr2_id ，并且都有 rating_id = 1 。

此外，还有一些加入和过滤的连接表列，我会省略，但我想我会提到它。

SQL Fiddle现在不适合我，但我已上传

我的查询到目前为止是这样：

  SELECT distinct（a1，a2），
 a1，
 a2 
 FROM 
（SELECT c。*，
最小（attr1_id，attr2_id）AS a1，
最大（attr1_id，attr2_id）AS a2 
从兼容性c 
 JOIN属性a ON c.attr1_id = a.id 
 JOIN PARAMETER pa ON a.parameter_id = pa.id 
 JOIN问题p ON pa.problem_id = p.id 
 WHERE p.id = 1 
 GROUP BY 1，
 2 HAVING NOT bool_or rating_id> 1））s; 
  

在示例中，总共有144个评分。每个用户创建了7个评分，其中有 rating_id> 1 ，并且在这14个评级中，2指的是相同的集合（ attr1_id ， attr2_id ）。
因此，我正在寻找的数字将是（77-12）= 65 。然而，这里的结果似乎是 77-2 = 75 。因此，只有具有相同属性ids存在的两个评分的行会被丢弃。

我还要指出我之前的问题，我被要求打开一个新的。

解决方案

我认为这是你所描述的：

从表t 
中选择最少（attr1_id，attr2_id）作为attr1， ）
 having bool_and（rating_d = 1）; 
  

我不明白你查询中的其他表，因为你从一个表您需要的一切。

I have the following table and some sample records in it:

  id  | attr1_id | attr2_id |      user_id      | rating_id |
------+----------+----------+-------------------+-----------+
 1    |      188 |      201 | [e-mail] |         3 |
 2    |      193 |      201 | [e-mail] |         2 |
 3    |      193 |      201 | [e-mail] |         1 |
 4    |      194 |      201 | [e-mail] |         1 |
 5    |      194 |      201 | [e-mail] |         1 |
 6    |      192 |      201 | [e-mail] |         1 |

The combination of (attr1_id, attr2_id, user_id) is UNIQUE, meaning each user can only create one record with a specific pair of attribute ids.

My goal is to select all distinct combinations of (attr1_id, attr2_id) where rating_id = 1, but only select each combiniation of attr1_id and attr2_id only once, and only where there doesn't exist any other row (by other users) that have rating_id > 1 and refer to the same attr1_id and attr2_id. Note that the combination of attr1_id and attr2_id can be switched around, so given these two records:

  id  | attr1_id | attr2_id |      user_id       | rating_id | override_comment
------+----------+----------+--------------------+-----------+------------------
  20  |       5  |       2  | [e-mail]  |         3 |
------+----------+----------+--------------------+-----------+------------------
  21  |       2  |       5  | [e-mail]  |         1 |

no row should be counted, as the rows refer to the same combination of attr_ids and one of them has rating_id > 1.

However, if these two rows exist:

  id  | attr1_id | attr2_id |      user_id       | rating_id | override_comment
------+----------+----------+--------------------+-----------+------------------
  20  |       5  |       2  | [e-mail]  |         1 |
------+----------+----------+--------------------+-----------+------------------
  21  |       2  |       5  | [e-mail]  |         1 |
------+----------+----------+--------------------+-----------+------------------
  22  |       2  |       5  | [e-mail]  |         1 |

all rows should only be counted as one, because they all share the same combination of attr1_id and attr2_id and all have rating_id = 1.

In addition, there is some joining and filtering by a joined table column which I'll leave out, but I thought I'd mention it anyway.

SQL Fiddle isn't working for me right now, but I've uploaded some sample data from the compatibility table.

My query so far is this:

SELECT distinct(a1, a2),
       a1,
       a2
FROM
  ( SELECT c.*,
           least(attr1_id, attr2_id) AS a1,
           greatest(attr1_id, attr2_id) AS a2
   FROM compatibility c
   JOIN attribute a ON c.attr1_id = a.id
   JOIN PARAMETER pa ON a.parameter_id = pa.id
   JOIN problem p ON pa.problem_id = p.id
   WHERE p.id = 1
   GROUP BY 1,
            2 HAVING NOT bool_or(rating_id > 1)) s;

In the sample, there are a total of 144 ratings. Each user has created 7 ratings that have a rating_id > 1 and of those 14 ratings, 2 refer to the same set of (attr1_id,attr2_id). Hence, the number I'm looking for would be (77-12) = 65. However, the result here seems to be 77-2 = 75. So only rows where two ratings with the same attribute ids exist, are discarded.

I would also point out my previous question for this matter where I was asked to open a new one.

解决方案

I think this does what you describe:

select least(attr1_id, attr2_id) as attr1, greatest(attr1_id, attr2_id) as attr2
from table t
group by least(attr1_id, attr2_id), greatest(attr1_id, attr2_id) 
having bool_and(rating_d = 1) ;

I don't understand the other tables in your query, because your start with a single table that has everything you need.

这篇关于PostgreSQL - 选择不同（column1，column2）条件所在的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！