mysql的关联子查询
时间：2010-04-14 来源：szuwcp
user表如下：id name street sex age 记录用户信息
pay表如下：no aid amount 记录用户的费用
其中id和no是主键，aid是外键
select * from user where id = (select aid from pay where no=3); 该子查询只返回一行，故不会报错。 如果子查询返回多行，则报错，比如： select * from user where id = (select aid from pay where no<4)； 报错如下：ERROR 1242 (21000): Subquery returns more than 1 row
改正办法：
使用any select * from user where id = any (select aid from pay where no<4)；
使用 in select * from user where id in (select aid from pay where no<4)；
使用 exists select * from user where exists (select * from pay where pay.aid=user.id and no<4);
select * from user where id = (select aid from pay where no=3); 该子查询只返回一行，故不会报错。 如果子查询返回多行，则报错，比如： select * from user where id = (select aid from pay where no<4)； 报错如下：ERROR 1242 (21000): Subquery returns more than 1 row
改正办法：
使用any select * from user where id = any (select aid from pay where no<4)；
使用 in select * from user where id in (select aid from pay where no<4)；
使用 exists select * from user where exists (select * from pay where pay.aid=user.id and no<4);
相关阅读 更多 +
