MySQL查重

MySQL 数据

CREATE TABLE &＃96;test&＃96; (&＃96;Id&＃96; int(11) NOT NULL AUTO_INCREMENT,&＃96;title&＃96; varchar(25) DEFAULT NULL COMMENT &＃39;标题&＃39;,&＃96;uid&＃96; int(11) DEFAULT NULL COMMENT &＃39;uid&＃39;,&＃96;money&＃96; decimal(2,0) DEFAULT &＃39;0&＃39;,&＃96;name&＃96; varchar(25) DEFAULT NULL,PRIMARY KEY (&＃96;Id&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;11 DEFAULT CHARSET&＃61;utf8mb4;INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;2&＃39;, &＃39;国庆节&＃39;, &＃39;2&＃39;, &＃39;12&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;3&＃39;, &＃39;这次是8天假哦&＃39;, &＃39;3&＃39;, &＃39;33&＃39;, &＃39;老顽童&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;4&＃39;, &＃39;这是Uid&＃61;1的第一条数据哦&＃39;, &＃39;1&＃39;, &＃39;70&＃39;, &＃39;欧阳锋&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;5&＃39;, &＃39;灵白山少主&＃39;, &＃39;4&＃39;, &＃39;99&＃39;, &＃39;欧阳克&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;7&＃39;, &＃39;九阴真经创始人&＃39;, &＃39;3&＃39;, &＃39;12&＃39;, &＃39;小顽童&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;8&＃39;, &＃39;双手互博&＃39;, &＃39;2&＃39;, &＃39;56&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;9&＃39;, &＃39;国庆节&＃39;, &＃39;2&＃39;, &＃39;19&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;10&＃39;, &＃39;蛤蟆功&＃39;, &＃39;1&＃39;, &＃39;57&＃39;, &＃39;欧阳锋&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;11&＃39;, &＃39;灵白山少主&＃39;, NULL, &＃39;0&＃39;, &＃39;欧阳克&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;12&＃39;, &＃39;国庆节&＃39;, &＃39;2&＃39;, &＃39;12&＃39;, &＃39;周伯通&＃39;);


单字段查询语句

select * from test where title in
(select title from test group by title having count(title) > 1)


SELECT COUNT(*),&＃96;title&＃96;,&＃96;uid&＃96; FROM &＃96;test&＃96;
GROUP BY &＃96;title&＃96; HAVING count(*) > 1;


SELECT *,COUNT(0) as c FROM &＃96;test&＃96; GROUP BY &＃96;title&＃96;;


SELECT *,COUNT(1) as c FROM &＃96;test&＃96; GROUP BY &＃96;title&＃96; HAVING c > 1;


select count(title) as &＃39;重复次数&＃39;,title from test
group by title having count(*)>1 order by title desc


多个字段重复记录查询

INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;2&＃39;, &＃39;国庆节&＃39;, &＃39;2&＃39;, &＃39;12&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;3&＃39;, &＃39;这次是8天假哦&＃39;, &＃39;3&＃39;, &＃39;33&＃39;, &＃39;老顽童&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;4&＃39;, &＃39;这是Uid&＃61;1的第一条数据哦&＃39;, &＃39;1&＃39;, &＃39;70&＃39;, &＃39;欧阳锋&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;5&＃39;, &＃39;灵白山少主&＃39;, &＃39;4&＃39;, &＃39;99&＃39;, &＃39;欧阳克&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;7&＃39;, &＃39;九阴真经创始人&＃39;, &＃39;3&＃39;, &＃39;12&＃39;, &＃39;小顽童&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;8&＃39;, &＃39;双手互博&＃39;, &＃39;2&＃39;, &＃39;56&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;9&＃39;, &＃39;国庆节&＃39;, &＃39;2&＃39;, &＃39;19&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;10&＃39;, &＃39;蛤蟆功&＃39;, &＃39;1&＃39;, &＃39;57&＃39;, &＃39;欧阳锋&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;11&＃39;, &＃39;灵白山少主&＃39;, NULL, &＃39;0&＃39;, &＃39;欧阳克&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;12&＃39;, &＃39;国庆节&＃39;, &＃39;2&＃39;, &＃39;12&＃39;, &＃39;周伯通&＃39;);
INSERT INTO &＃96;demo&＃96;.&＃96;test&＃96; (&＃96;Id&＃96;, &＃96;title&＃96;, &＃96;uid&＃96;, &＃96;money&＃96;, &＃96;name&＃96;) VALUES (&＃39;13&＃39;, &＃39;九阴真经创始人&＃39;, &＃39;3&＃39;, &＃39;0&＃39;, &＃39;小顽童&＃39;);

select *
from test t
where
(select count(1) from test where t.title &＃61; title AND t.uid &＃61; uid
)>1 ORDER BY t.title


UPDATE &＃96;test&＃96; SET &＃96;money&＃96;&＃61;&＃39;12&＃39; WHERE (&＃96;Id&＃96;&＃61;&＃39;13&＃39;)


SELECTtitle, count(title) as title_num,uid, count(uid) as uid_num
FROMtest
GROUP BY title, uid
HAVING (count(title) > 1)


select * from test a
where
(a.title,a.uid) in
(select title,uid from test group by title,uid having count(*) > 1)


SELECTtitle, count(title) as title_num,uid, count(uid) as uid_num
FROMtest
GROUP BY title, uid
HAVING (count(title) > 1)


全部数据

SELECT * FROM test
WHERE
(
title IN (SELECT title FROM test GROUP BY title,uid,money HAVING COUNT(*)>1)
AND
uid IN (SELECT uid FROM test GROUP BY title,uid,money HAVING COUNT(*)>1)
AND
money IN (SELECT money FROM test GROUP BY title,uid,money HAVING COUNT(*)>1)
)


全部数据