通过检查列值sql获取唯一行

尝试使用内部联接，如下所示：

    
      SELECT DISTINCT(id)
FROM table t1 INNER JOIN table t2
ON t1.id = t2.id
AND t1.isPresent != t2.isPresent
WHERE state = 2
</code>
  

看起来很简单

WHERE

条款

DISTINCT

做你解释的。

你需要第11 - 14行。

    
      SQL> with test (id, state, ispresent) as
  2    (select ‘id1’, 1, ‘N’ from dual union all
  3     select ‘id2’, 2, ‘N’ from dual union all
  4     select ‘id2’, 2, ‘Y’ from dual union all
  5     select ‘id3’, 3, ‘N’ from dual union all
  6     select ‘id4’, 4, ‘N’ from dual union all
  7     select ‘id4’, 4, ‘Y’ from dual union all
  8     select ‘id5’, 2, ‘N’ from dual union all
  9     select ‘id5’, 2, ‘Y’ from dual
 10    )
 11  select distinct id
 12  from test
 13  where ispresent = ‘N’
 14    and state = &par_id;
Enter value for par_id:   2
ID
id2
id5
SQL> /
Enter value for par_id: 4
ID
id4
SQL>
</code>
  

我想你想回归一个独特的

id

，只要它有两种情况（

N

和

Y

）同时为

isPresent

柱。因此，请考虑使用以下SQL语句

exists

操作员通过改变参数（现在是

2

）：

    
      with tab(id, state, isPresent) as
(
 select ‘id1’, 1, ‘N’  union all
 select ‘id2’, 2, ‘N’  union all
 select ‘id2’, 2, ‘Y’  union all
 select ‘id3’, 3, ‘N’  union all
 select ‘id4’, 4, ‘N’  union all
 select ‘id4’, 4, ‘Y’  union all
 select ‘id5’, 2, ‘N’  union all
 select ‘id5’, 2, ‘Y’
)
select distinct id
  from tab t1
 where exists ( select 1
                  from tab t2
                 where t2.state = t1.state
                   and t2.isPresent in (‘N’,’Y’) — this line might be commented out, if it’s certain that there’re only two cases “N” and “Y”
                 group by t2.state
                 having count(distinct t2.isPresent) = 2
               )
   and t1.state = 2 — try other values also such as 1, 3, 4
</code>