题目:写一条sql语句求3个表中互不重复的记录。
三个表设置如下:
CREATE TABLE `tw1` (
`user` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `tw1` VALUES ('张三', '24');
INSERT INTO `tw1` VALUES ('李四', '20');
INSERT INTO `tw1` VALUES ('李四', '22');
---------------------------------------------
CREATE TABLE `tw2` (
`user` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `tw2` VALUES ('张三', '20');
INSERT INTO `tw2` VALUES ('李四', '20');
---------------------------------------------
CREATE TABLE `tw3` (
`user` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `tw3` VALUES ('王五', '26');
INSERT INTO `tw3` VALUES ('赵六', '23');
INSERT INTO `tw3` VALUES ('张三', '20');
这里是需要求三个表的差集,用union all关联三个表,可以得到三个表所有记录的集合,再按一条记录进行分组,只查询仅有一条的记录,即为差集。
SELECT user, age FROM(
SELECT user, age FROM tw1
UNION ALL
SELECT user, age FROM tw2
UNION ALL
SELECT user, age FROM tw3) t
GROUP BY user,age
HAVING COUNT(*)=1;