用 EXISTS 子查询,如下:
SELECT *
FROM `TABLE1` as `A`
WHERE EXISTS (
SELECT * FROM `TABLE2` as B
where `A`.`FIELD1` = `B`.`FIELD2`
)
是没有问题的
如果用多级子查询,如下:
SELECT *
FROM `TABLE1` as `A`
WHERE EXISTS (
SELECT * FROM (
SELECT * FROM `TABLE2` as B
where `A`.`FIELD1` = `B`.`FIELD2`
) as T
)
就会出现 `A`.`FIELD1` 未知的错误
目前由于某种需要,必须要用这样的子查询,请问该如何解决呢?
SELECT *
FROM `TABLE1` as `A`
WHERE EXISTS (
SELECT * FROM `TABLE2` as B
where `A`.`FIELD1` = `B`.`FIELD2`
)
是没有问题的
如果用多级子查询,如下:
SELECT *
FROM `TABLE1` as `A`
WHERE EXISTS (
SELECT * FROM (
SELECT * FROM `TABLE2` as B
where `A`.`FIELD1` = `B`.`FIELD2`
) as T
)
就会出现 `A`.`FIELD1` 未知的错误
目前由于某种需要,必须要用这样的子查询,请问该如何解决呢?
-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`FIELD1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `table1` VALUES ('1');
INSERT INTO `table1` VALUES ('2');
INSERT INTO `table1` VALUES ('3');
INSERT INTO `table1` VALUES ('4');
INSERT INTO `table1` VALUES ('5');
INSERT INTO `table1` VALUES ('6');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for table2
-- ----------------------------
DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
`FIELD2` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 18432 kB';-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `table2` VALUES ('1');
INSERT INTO `table2` VALUES ('2');
INSERT INTO `table2` VALUES ('3');用以下SQL语句查询:
SELECT * FROM TABLE1 A
WHERE EXISTS(
SELECT * FROM TABLE2 B
WHERE A.FIELD1 = B.FIELD2
)
结果集为
FIELD1
1
2
3用以下SQL语句:
SELECT * FROM TABLE1 A
WHERE EXISTS(
SELECT * FROM TABLE2 B
WHERE A.FIELD1 = B.FIELD2
)
出现错误提示:
Unknown column 'A.FIELD1' in 'where clause'目前由于某种特殊需要,必须使用这样的语句形式
请各位大牛帮忙
2 关联查询会使你SQL 更加容易清晰。
3 提供一下你MySQL 具体版本很可能是Bug