MySQL 查重

MySQL 数据

CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT NULL COMMENT '标题',
  `uid` int(11) DEFAULT NULL COMMENT 'uid',
  `money` decimal(2,0) DEFAULT '0',
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

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

单字段查询语句

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

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

SELECT *,COUNT(0) as c FROM `test` GROUP BY `title`;

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

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

多个字段重复记录查询

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

select 
  *
from 
  test t
where
(
     select count(1) from test where t.title = title 
		 AND 
		 t.uid = uid
)>1 ORDER BY t.title

UPDATE `test` SET `money`='12' WHERE (`Id`='13')

SELECT
    title, count(title) as title_num,
    uid, count(uid) as uid_num
FROM
    test
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)

SELECT
    title, count(title) as title_num,
    uid, count(uid) as uid_num
FROM
    test
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)
)

全部数据