mysql统计重复字段个数_MySQL：统计两表重复数据出现次数

统计重复数据出现次数

SELECT * FROM

(

SELECT * FROM

(

SELECT count(t1.compare_id) AS c1,t1.*

FROM t1

LEFT JOIN t3 ON t1.compare_id=t3.compare_id

WHERE t1.compare_id=t3.compare_id

GROUP BY t1.compare_id

) t

) a1

LEFT JOIN

(

SELECT * FROM

(

SELECT count(t2.compare_id) AS c2,t2.*

FROM t2

LEFT JOIN t3 ON t2.compare_id=t3.compare_id

WHERE t2.compare_id=t3.compare_id

GROUP BY t2.compare_id

) t WHERE t.c2>1

) a2 ON a1.compare_id = a2.compare_id

WHERE a1.compare_id = a2.compare_id