mysql重复数据得处理

SELECT
*
FROM
sr_sjycpc
WHERE
fk_id IN (
SELECT
fk_id
FROM
sr_sjycpc a,
mz_xzjg b
WHERE
a.sys_xzqh = b.xzqh_id
AND sys_clbj = 0
AND type = 1
AND mdjlx = '1'
AND b.node_id LIKE '33%'
GROUP BY
mhzsfz
HAVING
COUNT(mhzsfz) > 1
)
AND sys_clbj = 0
AND type = 1
AND mdjlx = '1'
AND pk_id NOT IN (
SELECT
min(pk_id)
FROM
sr_sjycpc a,
mz_xzjg b
WHERE
a.sys_xzqh = b.xzqh_id
AND sys_clbj = 0
AND type = 1
AND mdjlx = '1'
AND b.node_id LIKE '33%'
GROUP BY
mhzsfz
HAVING
COUNT(mhzsfz) > 1
)

-----------------------------------------------------------

SELECT
mhzsfz,fk_id,a.pk_id ,concat('update sr_sjycpc set sys_clbj=1 where pk_id=\'',pk_id,'\';')
FROM
sr_sjycpc a,
mz_xzjg b
WHERE
a.sys_xzqh = b.xzqh_id
AND sys_clbj = 0
AND type = 2
AND mdjlx = '1'
AND b.node_id LIKE '33%'
GROUP BY
mhzsfz
HAVING
COUNT(mhzsfz) > 1

--------------------------------------------------------------------------

//将需要处理的数据筛选后放进临时表

CREATE table linshi15
select * from sr_sjycpc where type = 0 and mdjlx = 1 and sys_xzqh like '330127%' and sys_clbj = 0;

INSERT into linshi15
select * from sr_sjycpc where type = 0 and mdjlx = 2 and sys_xzqh like '330127%' and sys_clbj = 0;
select * from sr_sjycpc where type = 0 and mdjlx = 3 and sys_xzqh like '330127%' and sys_clbj = 0;

CREATE table linshi16
select * from sr_sjycpc where type = 1 and mdjlx = 1 and sys_xzqh like '330127%' and sys_clbj = 0;

INSERT into linshi16
select * from sr_sjycpc where type = 1 and mdjlx = 2 and sys_xzqh like '330127%' and sys_clbj = 0;
INSERT into linshi16
select * from sr_sjycpc where type = 1 and mdjlx = 3 and sys_xzqh like '330127%' and sys_clbj = 0;
INSERT into linshi16
select * from sr_sjycpc where type = 2 and mdjlx = 1 and sys_xzqh like '330127%' and sys_clbj = 0;
INSERT into linshi16
select * from sr_sjycpc where type = 2 and mdjlx = 2 and sys_xzqh like '330127%' and sys_clbj = 0;
INSERT into linshi16
select * from sr_sjycpc where type = 2 and mdjlx = 3 and sys_xzqh like '330127%' and sys_clbj = 0;

-----------------------------------------------------------------

//先查找一遍，检查下
select * from sr_main where PK_SR_MAIN in ( select fk_id from linshi15 where fk_id not in ( select fk_id from linshi16 ) )
select * from linshi15 where fk_id not in ( select fk_id from linshi16 )
-----------------------------------------------------------------

//最后进行处理
UPDATE sr_main set mkhyh='13',mkhr=mhz,main_bz_sam07=mhzsfz,mjtrk=mxsrs where PK_SR_MAIN in ( select fk_id from linshi15 where fk_id not in ( select fk_id from linshi16 ) ) and mhzsfz in ( select sfz from stjz ) ;
UPDATE sr_sjycpc a,linshi15 b set sys_clbj ='1',bczt ='1' where a.pk_id=b.pk_id and fk_id not in ( select fk_id from linshi16 );