本文主要是介绍检查重复语句的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
)
这篇关于检查重复语句的sql二中比较的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
