SQL理论:在一列中筛选出重复项，在另一列中选择最低值

Question

我试图找出从结果集中删除行的最佳方法，其中一个列中的值或另一个列中的值在结果集中重复。

假设查询的结果如下：

a_value | b_value
-----------------
   1    |   1
   2    |   1
   2    |   2
   3    |   1
   4    |   3
   5    |   2
   6    |   4
   6    |   5

我想做的是：

• 删除a_value中具有重复值的所有行
• 只为给定的b_value选择1行

因此，我希望在消除a_value重复之后，过滤的结果会像这样结束：

a_value | b_value
-----------------
   1    |   1
   3    |   1
   4    |   3
   5    |   2

然后在只选择一个b_value之后，就像这样：

a_value | b_value
-----------------
   1    |   1
   4    |   3
   5    |   2

对于如何通过SQL高效地完成这项任务，我将不胜感激。

Answer 1

with
     q_res ( a_value, b_value ) as (
       select 1, 1 from dual union all
       select 2, 1 from dual union all
       select 2, 2 from dual union all
       select 3, 1 from dual union all
       select 4, 3 from dual union all
       select 5, 2 from dual union all
       select 6, 4 from dual union all
       select 6, 5 from dual
     )
-- end test data; solution begins below
select   min(a_value) as a_value, b_value
from     ( 
           select   a_value, min(b_value) as b_value
           from     q_res
           group by a_value
           having   count(*) = 1
         )
group by b_value
order by a_value   -- ORDER BY is optional
;

A_VALUE  B_VALUE
-------  -------
      1        1
      4        3
      5        2

Answer 2

1)在内部查询中，我避免了a_value列中存在的所有重复项，并从输入表中获取所有剩余的行并存储它们。

as t2. By joining t2 with t1 there would be full data without any dups as per     your #1 in requirement.

 SELECT t1.*
   FROM Table t1,
      (
        SELECT a_value       
          FROM Table 
         GROUP BY a_value
         HAVING COUNT(*) = 1
       ) t2
   WHERE t1.a_value = t2.a_value; 

2)一旦获得过滤后的数据，我将对步骤1中获得的过滤数据集中的每一行进行排序，并且只选择带有rank=1的行。

SELECT X.a_value,
       X.b_value
  FROM
     (
        SELECT t1.*,
               ROW_NUMBER() OVER ( PARTITION BY t1.b_value ORDER BY t1.a_value,t1.b_value ) AS rn 
          FROM Table t1,
             (
               SELECT a_value       
                 FROM Table 
                GROUP BY a_value
               HAVING COUNT(*) = 1
              ) t2
         WHERE t1.a_value = t2.a_value 
       ) X
WHERE X.rn = 1;