67,549
社区成员




create table TEST_CHECK_DATA
(
mpiid INTEGER not null,
name VARCHAR2(20),
phone_number VARCHAR2(15),
id_no VARCHAR2(20)
); /* 建测试表*/
(select t1.mpiid, t1.name, t1.phone_number, t1.id_no from test_check_data t1 inner join ( /*name, phone_number*/
select name, phone_number, count(1) as cnt from test_check_data group by name, phone_number) t2 on t1.name = t2.name and t1.phone_number = t2.phone_number and t2.cnt >1)
union all
(select t1.mpiid, t1.name, t1.phone_number, t1.id_no from test_check_data t1 inner join ( /*name, id_no*/
select name, id_no, count(1) as cnt from test_check_data group by name, id_no) t2 on t1.name = t2.name and t1.id_no = t2.id_no and t2.cnt >1)
union all
(select t1.mpiid, t1.name, t1.phone_number, t1.id_no from test_check_data t1 inner join ( /*id_no, phone_number*/
select id_no, phone_number, count(1) as cnt from test_check_data group by id_no, phone_number) t2 on t1.id_no = t2.id_no and t1.phone_number = t2.phone_number and t2.cnt >1)
如果还达不到要求的话, 可以先将phoneNumberSelf,idNo在数据库排好序, 这样我们只需要一个临时变量和一次循环即可取出二个及以上属性个数大于2的值.
不过建议这种逻辑还是在插库的时候提前处理, 每次这么查询一次都要进去这种逻辑处理很浪费时间, 首先增加一个字段, 在插入, 更新, 删除 数据的时候先判断新数据的属性是否存在2个以上, 如果存在就把这个新增的字段置1, 这样查询的时候只需要取出新增字段值为1的数据即可.