MySQL 查重
mysql 查重
2023-09-11 14:14:56 时间
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)
)
全部数据
相关文章
- Pandas 中的 SQLAlchemy ,create_engine清理数据库连接,pandas读取及存储MySQL
- mysql安装教程以及配置快捷方式
- MySQL无法启动报 Error: could not open single-table tablespace file ./mysql/innodb_table_sta
- [转]mysql字符串拆分实现split功能
- [MySQL] mysql地理位置服务geometry字段类型
- 连接Mysql提示Can’t connect to local MySQL server through socket的解决方法
- Brew 卸载MySql以及安装Mysql
- mysql 几阶b树_在学到MySQL索引时,涉及到了B树和B+树.聊一聊
- mysql 编译安装 window篇
- 项目访问mysql时报: Failed to obtain JDBC Connection...:Host ‘X‘is not allowed to connect to this MySQL ser
- 【mysql】MySQL的sql_mode模式说明及设置
- 【mysql问题】解决2003-Can‘t connect to MySQL server on ‘ ‘(10060“Unknown error“)
- Mysql迁移由于字符集导致乱码的数据
- JDBC 连接 MySQL 异常:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
- MySQL基础之 支持的数据类型
- Linux+Mysql+Apache+Php测试环境部署
- centos mySql 安装
- PostgreSQL通过mysql_fdw访问MySQL数据库
- (5.5)mysql高可用系列——MySQL半同步复制(实践)
- (5.4)mysql高可用系列——MySQL异步复制(实践)
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- 不同数据库、不同数据表进行数据同步,带有日志记录、数据校验、全量同步、增量同步、数据量自定义功能,满足mysql、oracle等主流数据库进行跨库数据传输、备份、同步。
- Mysql主从报错锦集
- MySQL 安装mysql数据库