代码之家 › 专栏 › 技术社区 › Ingo Bürk

查找某些行数相同,但另一列上不同的行数

postgresql sql

-2

Ingo Bürk · 技术社区 · 7 年前

假设我有下表:

CREATE TABLE data (
  PROJECT_ID VARCHAR,
  TASK_ID VARCHAR,
  REF_ID VARCHAR,
  REF_VALUE VARCHAR
);

我想识别行,其中

PROJECT_ID , REF_ID , REF_VALUE 都是一样的
但是 TASK_ID 是不同的。

所需输出是以下内容的列表: TASK_ID_1 , TASK_ID_2 和 COUNT(*) 这种冲突。例如,

DATA
+------------+---------+--------+-----------+
| PROJECT_ID | TASK_ID | REF_ID | REF_VALUE |
+------------+---------+--------+-----------+
|      1     |    1    |    1   |     1     |
|      1     |    1    |    1   |     2     |
|      1     |    2    |    1   |     1     |
|      1     |    2    |    1   |     2     |
+------------+---------+--------+-----------+


OUTPUT
+-----------+-----------+----------+
| TASK_ID_1 | TASK_ID_2 | COUNT(*) |
+-----------+-----------+----------+
|     1     |     2     |     2    |
|     2     |     1     |     2    |
+-----------+-----------+----------+

这意味着有两个条目 TASK_ID == 1 和两个带有 TASK_ID == 2 其他三列的值相同。输出中固有的对称性很好。

我该如何找到这些信息?我尝试将表连接到自身并进行分组,但这在单个任务中得到的结果比表中的所有行都多,因此这显然是错误的。

所使用的数据库是PostgreSQL,但最好采用适用于大多数常见SQL系统的解决方案。

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

您需要自加入和聚合:

select d1.task_id as task_id_1, d2.task_id as task_id_2, count(*)
from data d1 join
     data d2
     on d1.project_id = d2.project_id and
        d1.ref_id = d2.ref_id and
        d1.ref_value = d2.ref_value and
        d1.task_id <> d2.task_id
group by d1.task_id, d2.task_id;

笔记:

添加条件 d1.task_id < d2.task_id 如果希望每个配对在结果集中只出现一次。
这无法处理 NULL 值,尽管这很容易处理。使用 is not distinct from = .

您还可以使用 using 条款:

select d1.task_id as task_id_1, d2.task_id as task_id_2, count(*)
from data d1 join
     data d2
     using (project_id, ref_id, ref_value)
where d1.task_id <> d2.task_id
group by d1.task_id, d2.task_id;

您可以使用以下命令了解可能返回的行数:

select d.project_id, d.ref_id, d.ref_value, count(distinct d.task_id), count(*)
from data d
group by d.project_id, d.ref_id, d.ref_value;

Juan Carlos Oropeza 7 年前

这就是我如何理解你的问题。这假设同一组合只有两个任务。

SQL DEMO

SELECT "PROJECT_ID", "REF_ID", "REF_VALUE",
       MIN("TASK_ID") as TASK_ID_1,
       MAX("TASK_ID") as TASK_ID_2,
       COUNT(*) as cnt
FROM Table1       
GROUP BY "PROJECT_ID", "REF_ID", "REF_VALUE"
HAVING MIN("TASK_ID") != MAX("TASK_ID") 
    -- COUNT(*) > 1 also should work

输出

我添加了更多的专栏,以明确哪些是相同的元素:

| PROJECT_ID | REF_ID | REF_VALUE | task_id_1 | task_id_2 | cnt |
|------------|--------|-----------|-----------|-----------|-----|
|          1 |      1 |         2 |         1 |         2 |   2 |
|          1 |      1 |         1 |         1 |         2 |   2 |