如何使用SQL/mySQL选择两列具有多个相同值且一列具有不同值的行？

Question

exTab

 PK     col1     col2     col3
 ---------------------------------
 1      val1     val4     val7   **want to return this row only
 2      val1     val4     val8
 3      val1     val4     val8
 4      val1     val5     val9
 5      val2     val5     val9
 6      val2     val5     val9
 7      val2     val6     val0
 8      val3     val6     val0

如何使用SQL（与mySQL一起）只返回col1中具有多个相同值、col2中具有多个相同值、但COL3中具有唯一值的行？

例如，在上面的表（exTab）中，val1在col1中出现了4次，对于这4次，val4在col2中出现了3次，但是对于这3次，val7在col3中只出现了一次，所以我想返回这一行（第1行）。考虑到这些条件，第1行将是我希望从此表返回的唯一一行。

我试过和group by的各种组合

Answer 1

你可以尝试以下几点：

SELECT
    *
    FROM table 
    WHERE col1 IN (SELECT col1 FROM table GROUP BY 1 HAVING count(*)>1)
    AND col2 IN (SELECT col2 FROM table GROUP BY 1 HAVING count(*)>1)
    AND col3 IN (SELECT col3 FROM table GROUP BY 1 HAVING count(*)=1)

尽管如果你的桌子很大，表现可能会很糟糕。

Answer 2

如果我很好地理解这个问题，SQL查询可能会帮助你：

SELECT 
    SubTab.PK 
FROM 
    (SELECT  
         PK, 
         COUNT(col3) OVER (PARTITION BY col1) as col1_group,
         COUNT(col3) OVER (PARTITION BY col2) as col2_group
     FROM 
         exTab) SubTab
WHERE 
    SubTab.col1_group = 1 AND SubTab.col2_group = 1;

它将在原始选项卡上运行两个窗口聚合函数，然后返回临时选项卡，从该选项卡中，我们只选择col3在一个组和另一个组中唯一的行。

Answer 3

我将通过合并两个子查询的结果来做到这一点：

在子查询1中，我将得到不止一次出现的col1-col2组合。

在子查询2中，我会得到只出现一次的col1-col2-col3组合。

这两个子查询的交集（内部联接）将产生您正在寻找的记录。

select t1.*
from
    exTab t1
    inner join
        (select col1, col2 from exTab
         group by col1, col2
         having count(*)>1) t2
    inner join
        (select col1, col2, col3 from exTab
         group by col1, col2, col3
         having count(*)=1) t3 on t2.col1=t3.col1
                              and t2.col2=t3.col2
                              and t1.col1=t3.col1
                              and t1.col2=t3.col2
                              and t1.col3=t3.col3