第一个表结构
DROP TABLE IF EXISTS `a_test`;
CREATE TABLE `a_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tId` int(11) DEFAULT NULL,
`val` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
INSERT INTO `a_test` VALUES (1, 11, '主设备');
INSERT INTO `a_test` VALUES (2, 23, '主设备');
INSERT INTO `a_test` VALUES (3, 36, '关联设备');
INSERT INTO `a_test` VALUES (4, 47, '主设备');
INSERT INTO `a_test` VALUES (5, 58, '关联设备');
INSERT INTO `a_test` VALUES (6, 67, '关联设备');
第二个表
DROP TABLE IF EXISTS `a_test_class`;
CREATE TABLE `a_test_class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`testId` int(11) DEFAULT NULL,
`tId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
INSERT INTO `a_test_class` VALUES (1, 3, 32);
INSERT INTO `a_test_class` VALUES (2, 5, 11);
INSERT INTO `a_test_class` VALUES (3, 6, 22);
查询SQL一
select A.id,A.val,A.tId from a_test as A,a_test_class as B where A.tId = 11 or (B.tId = 11 and B.testId = A.Id)
查询SQL二
select A.id,A.val,A.tId from a_test_class as B inner join a_test as A on A.tId = 11 or (B.tId = 11 and B.testId = A.Id)
表二的tesdId关联表一的id,在两个表中查询 A.tId和B.tId符合查询条件的数据,也就是一个编号可能存在于表一的tId,也可能存在与表2的tId,只要符合条件就输出结果,但是上面的SQL执行后会出现id重复的数据,请教如何解决?
解决