一张数据表有CODE,EXP_CODE,user,time四个字段现想找出该表中CODE和EXP_CODE两个字段都相同的记录如果用下面的语句select * from audit_confirm_info where EXP_CODE='R8_002' and CODE in(select CODE from audit_confirm_info group by CODE having count(*) > 1) order by CODE;只能找出CODE相同的记录,请问该语句该如何改???
而是EXP_CODE和CODE的值都相同的记录
----------------------------------
还是没说清。你举个例子,什么是CODE和EXP_CODE都相同,且CODE!=EXP_CODE
10100 R8_002 lr 10:10
10100 R8_002 lj 11:30
10101 R9_001 lz 12:30
..........要找出
10100 R8_002 lr 10:10
10100 R8_002 lj 11:30
select a.* from audit_confirm_info a inner join (select a.EXP_CODE,a.CODE from audit_confirm_info a left join audit_confirm_info b on a.EXP_CODE = b.EXP_CODE and a.CODE = b.CODE group by EXP_CODE,CODE having count(*) > 1) tmp on a.EXP_CODE = tmp.EXP_CODE and a.CODE = tmp.CODE where a.EXP_CODE='R8_002'