create table ta(id int, name varchar(10));
insert into ta(id, name) values(1, 'name1'), (2, 'name2'), (3, 'name3'), (4, 'name4');create table tb(id int, rid int);
insert into tb(id, rid) values(1, 78), (2, 64), (2, 78), (3, 78), (4, 78), (4, 64),(4, 56); select ta.id, ta.name, tb.rid
from ta, tb
where ta.id = tb.id and tb.rid = 64 and ta.id in
(select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78) order by ta.id desc;
对于上边的子查询,我本以为是先取得子查询的结果集,然后再于改结果集中查找,
可是经过测试子查询好像并不是完全将结果列出来后再供父查询查找,也是基于父查询的筛选结果再去子查询的结果集中匹配!
改下:select ta.id, ta.name, tb.rid
from ta, tb
where ta.id = tb.id and tb.rid = 64 and ta.id in
(select a.id from ta a , tb b where a.id = b.id and b.rid = 78) order by ta.id desc;