单表仅查询重复数据
1.首先建一张表
-- 建表tttttt
DROP TABLE IF EXISTS `tttttt`;
CREATE TABLE `tttttt` (
`t` int(10) NULL DEFAULT NULL,
`y` int(10) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci
-- 插入测试数据
INSERT INTO `tttttt` VALUES (1, 1);
INSERT INTO `tttttt` VALUES (1, 1);
INSERT INTO `tttttt` VALUES (2, 2);
INSERT INTO `tttttt` VALUES (2, 3);
INSERT INTO `tttttt` VALUES (3, 3);
INSERT INTO `tttttt` VALUES (8, 8);
INSERT INTO `tttttt` VALUES (8, 8);
可以得到下列表数据。很明显,有四行数据是完全重复的
2.仅查询重复数据。
思路:这时候我们可以使用自关联查询。将tttttt表虚拟化(起别名)出第二张表,然后与表tttttt本身比较。然后使用count函数计数,当符合两张表的两列数据完全相等时加1。最后通过count()>1来过滤掉那些不重复的数据。
SELECT a.* FROM tttttt a WHERE ( SELECT COUNT(*) FROM tttttt WHERE tttttt.t= a.t AND tttttt.y = a.y) > 1;
多表仅查询重复数据
1.创建yyyyyy表
-- 创建yyyyyy表
DROP TABLE IF EXISTS `yyyyyy`;
CREATE TABLE `yyyyyy` (
`q` int(10) NULL DEFAULT NULL,
`w` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci
-- 插入测试数据
INSERT INTO `yyyyyy` VALUES (1, '1');
INSERT INTO `yyyyyy` VALUES (8, '1');
INSERT INTO `yyyyyy` VALUES (3, '1');
插入数据后,可以得到下列数据。
然后用tttttt使用inner join关联yyyyyy表。得到下列数据。
思路:即使是多表查询,最后查出来的结果我们可以作为中间表使用。中间表自关联,需要查询的字段对比,还是使用count(*)来计数,最后过滤掉那些不大于1的数据行。
SELECT zb.* FROM
(SELECT a.*,b.* FROM tttttt a INNER JOIN yyyyyy b ON a.t = b.q) zb
WHERE
(SELECT COUNT(*) FROM (SELECT a.*,b.* FROM tttttt a INNER JOIN yyyyyy b ON a.t = b.q) zb1
WHERE zb.t = zb1.t AND zb.y = zb1.y AND zb.q = zb1.q AND zb.w = zb1.w) > 1;
执行结果如下: