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);
阅读(327) | 评论(0) | 转发(0) |