SQL有助于按组和计数查找唯一对

4 回答

• 0

  您可以使用 APPLY 检查 ColB 是否有多于1个取消 ColA 值的值：

  WITH Cte(ColA, ColB) AS(
      SELECT * FROM( VALUES
          (1, 'A'), (1, 'A'), (2, 'B'), (3, 'B'), (4, 'C'), (4, 'C'), (5, 'C')
      ) t(a,b)
  )
  SELECT DISTINCT c1.*
  FROM Cte c1
  CROSS APPLY(
      SELECT COUNT(*) AS cnt
      FROM Cte c2
      WHERE
          c2.ColB = c1.ColB
          AND c2.ColA <> c1.ColA
      GROUP BY c2.ColB
      HAVING COUNT(*) > 0
  ) x
  

  在线演示

  回复于 2024-05-10T18:19:01+08:00
• 0

  首先使用Group By Clause和Row_Number（）我将得到一个数字序列结果集 . 通过这种方式，我可以识别ColumnA和ColumnB的唯一对，其中对于相同的ColumnB，有多个不同的ColumnA值 .

  Select
      ColumnA,
      ColumnB,
      Row_Number() Over(Partition By ColumnB Order By ColumnA) As RowNum
  From SimpleTable
  Group By ColumnB, ColumnA;
  

  输出：

  ColumnA ColumnB RowNum
  1       A       1
  2       B       1
  3       B       2
  4       C       1
  5       C       2
  

  现在，您可以将此结果放入表表达式 - CTE或派生表（我选择CTE）并仅过滤掉RowNum大于或等于2的ColumnB值 . 因此，最终查询将为 -

  ;With CTE
  As
  (
      Select
          ColumnA,
          ColumnB,
          Row_Number() Over(Partition By ColumnB Order By ColumnA) As RowNum
      From SimpleTable
      Group By ColumnB, ColumnA
  )
  Select ColumnA, ColumnB From CTE
  Where ColumnB In (Select ColumnB From CTE Where RowNum >=2)
  Order By ColumnA, ColumnB;
  

  最终输出：

  ColumnA ColumnB
  2       B
  3       B
  4       C
  5       C
  

  希望这有用:)

  回复于 2024-05-10T18:19:01+08:00
• 0

  我建议使用 min() 和 max() 作为窗口函数的简单方法：

  select colA, colB
  from (select t.*,
               min(colA) over (partition by colB) as mincolA,
               max(colA) over (partition by colB) as maxcolA
        from t
       ) t
  where mincolA <> maxcolA;
  

  回复于 2024-05-10T18:19:01+08:00
• 0

  编辑：对第一个答案道歉，我没有注意到 ColA 的规定在 ColB 上有多个不同的值 . 这是我更新的答案：

  SELECT ColA, ColB
  FROM test
  WHERE EXISTS (
      SELECT ColB
      FROM test AS subtest
      WHERE test.ColB = subtest.ColB
      GROUP BY ColB
      HAVING COUNT(DISTINCT ColA) > 1
  )
  GROUP BY ColA, ColB
  

  回复于 2024-05-10T18:19:01+08:00