MySQL查询重复记录的最新一条记录

目录

一、背景

二、测试环境准备

三、基本SQL语句

四、参考文献


一、背景

最近在做一个黑名单的需求,但是由于历史原因,黑名单有大量的重复数据,比如一个user_id = 001 的用户,有效status=1,无效status=0,但是实际产线数据库有4条数据(3条无效,1条有效),故需要找出重复数据的最新一条数据,并将user_id设置为索引,不允许重复。

二、测试环境准备

新建表t_user,并插入测试数据

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户编码',
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '密码',
  `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 134 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '用户信息表' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (2, 'u_001', 'liudehua', '123456', 0, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (3, 'u_933', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (4, 'u_586', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (5, 'u_662', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (6, 'u_89', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (7, 'u_8476', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (8, 'u_967', 'liudehua', '123456', 1, '2022-01-06 09:18:40', '2022-01-06 09:18:40');
INSERT INTO `t_user` VALUES (124, 'u_001', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (125, 'u_933', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (126, 'u_586', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (127, 'u_662', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (128, 'u_89', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (130, 'u_96', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (131, 'u_449', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');
INSERT INTO `t_user` VALUES (132, 'u_4330', 'kucun', NULL, 0, '2022-01-21 09:20:42', '2022-01-21 09:20:42');

三、基本SQL语句

查询数量

SELECT count(*) from t_user; -- 查询数量

查询所有重复数据的user_id以及重复条数

SELECT u.user_id, COUNT(*) from t_user u group by u.user_id HAVING COUNT(1) > 1; -- 查询重复的数据以及重复条数

拓展:查询所有不重复数据的user_id

SELECT u.user_id from t_user u group by u.user_id HAVING COUNT(1) = 1; -- 查询不重复的数据

查询所有重复数据

SELECT * from t_user u2 where u2.user_id in(
SELECT u.user_id from t_user u group by u.user_id HAVING COUNT(1) > 1); -- 查询所有重复的数据

查询最新一条数据

SELECT * from t_user where id = (SELECT MAX(id) from t_user); -- 查询最新一条数据

根据时间查询重复记录的最新一条数据(这个SQL有问题,因为如果update_time相同的话,将会导致结果仍旧有重复数据,我们这个demo就是这个情况)

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.update_time in (SELECT MAX(u2.update_time) from t_user u2 group by u2.user_id);
-- 根据时间查询最新的数据(查询结果为15条,实际为10条,所以有问题)

根据id查询记录的最新一条数据(因为id是主键,是不会重复的,所以没有问题)

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.id in (SELECT MAX(u2.id) from t_user u2 group by u2.user_id);
-- 根据id查询最新的数据(记录为10条)

拓展:查询重复数据的第一条记录

SELECT u.user_id, u.username, u.create_time, u.update_time from t_user u where u.id in (SELECT min(u2.id) from t_user u2 group by u2.user_id);
-- 根据id查询第一条记录(记录为10条)

四、参考文献

1、mysql查询出所有重复的记录

https://www.cnblogs.com/phpfensi/p/10214487.html

上一篇:改造一下jeecg中的部门树


下一篇:php laravel 多条件筛选