数据库重复数据过滤

[dotty_csdn]

一个表有a,b两个字段，b有生效和失效两个状态，要保证b为生效状态时，a字段唯一，有没有数据库解决方案，失效状态可能是多个，所以ab联合不是唯一的

[卖水果的net]

SQL> create table t(a int, b int);
Table created
SQL> create unique index iux_t_ab on t(decode(b, 0, null,a));
Index created
SQL> -- b = 0 时，a可以有重复值
SQL> insert into t values(100, 1);
1 row inserted
SQL> insert into t values(100, 0);
1 row inserted
SQL> insert into t values(100, 0);
1 row inserted
SQL> insert into t values(200, 1);
1 row inserted
SQL> insert into t values(200, 1);  -- 这行失败
insert into t values(200, 1)
ORA-00001: 违反唯一约束条件 (ORACLE.IUX_T_AB)
SQL> insert into t values(200, 0);
1 row inserted
SQL> select * from t;
   A    B
---- ----
 100    1
 100    0
 100    0
 200    1
 200    0
SQL> drop table t purge;
Table dropped

SQL> 

[aaaa_007]

数据量少
select * from table where a in (
select a from table where b='有效' group by a having count(*)=1);

数据量多，子查询建临时表