检查重复语句的sql二中比较

//查询重复的order_sn,goods_code

SELECT id, order_sn,goods_code ,atime from ecs_order_goods s 

WHERE 

(s.order_sn,s.goods_code) 

IN

(select order_sn,goods_code from ecs_order_goods GROUP BY order_sn,goods_code  having count(*) >1 )

--（同上，查询重复的order_sn,goods_code）----------------------------------------------

select * from 

(SELECT

id,

order_sn,

goods_code,

atime,

IF(

@pa = CONCAT(ff.goods_code, ff.order_sn)  ,@rank :=@rank + 1 ,@rank := 1

)AS rank ,@pa :=  CONCAT(ff.goods_code, ff.order_sn) AS pa

FROM

(

SELECT

id,

order_sn,

goods_code,

atime,

count(1)

FROM

`ecs_order_goods `

GROUP BY

order_sn,

goods_code,

atime desc

)ff,

(

SELECT

@rank := 0 ,@rownum := 0 ,@pa = NULL

)tt) res

where rank > 1

===删除重复添加的条数，按插入时间最久===========================

DELETE

FROM

ecs_order_goods 

WHERE

id IN(

SELECT

res.id

FROM

(

SELECT

id,

order_sn,

goods_code,

atime,

IF(

@pa = CONCAT(ff.goods_code, ff.order_sn),@rank :=@rank + 1 ,@rank := 1

新增检查同一个表中存在重复的

例如：表中字段unionid，是否重复

SELECT * FROM ecm_lease_user_weixin WHERE unionid IN(
SELECT unionid FROM ecm_lease_user_weixin GROUP BY unionid HAVING COUNT(unionid) > 1
);

检查 `mina_openid`,`unionid` 的值是否重复

SELECT * from (SELECT *, CONCAT(`mina_openid`,`unionid`) as nameAndCode from ecm_lease_user_weixin) t WHERE t.nameAndCode in 
(
    SELECT nameAndCode from (SELECT CONCAT(`mina_openid`,`unionid`) as nameAndCode from ecm_lease_user_weixin) tt GROUP BY nameAndCode HAVING count(nameAndCode) > 1
)