子查询还要有一个关联的条件:IdDirectory=LiaoLiao.IdDirectory select IdCatalog from PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory and dCatalog=7 and IdDirectory=5)
引语句中:select * from PVC管件Detail LiaoLiao where IdCatalog in (select IdCatalog from PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory and dCatalog=7 and IdDirectory=5) PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory是应该限制select IdCatalog from PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory and dCatalog=7 and IdDirectory=5)的查询,而不应该限制select * from PVC管件Detail LiaoLiao where IdCatalog in (...)的查询。想不通
select * from PVC管件Detail a where exists (select IdCatalog from PVC管件Catalog b where b.IdDirectory=a.IdDirectory and dCatalog=7 and IdDirectory=5) select * from PVC管件Detail LiaoLiao where IdCatalog in (select IdCatalog from PVC管件Catalog where dCatalog=7 and IdDirectory=5)你可以比较一下这2个语句的区别。
把IdCatalog = 7 的所有记录检索出来了,并没有限制
PVC管件Catalog.IdDirectory = PVC管件Detail.IdDirectory
所以结果集肯定不一样了
引语句中:select * from PVC管件Detail LiaoLiao where IdCatalog in (select IdCatalog from PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory and dCatalog=7 and IdDirectory=5)
PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory是应该限制select IdCatalog from PVC管件Catalog where IdDirectory=LiaoLiao.IdDirectory and dCatalog=7 and IdDirectory=5)的查询,而不应该限制select * from PVC管件Detail LiaoLiao where IdCatalog in (...)的查询。想不通
where exists
(select IdCatalog from PVC管件Catalog b where b.IdDirectory=a.IdDirectory and dCatalog=7 and IdDirectory=5)
select * from PVC管件Detail LiaoLiao where IdCatalog in (select IdCatalog from PVC管件Catalog where dCatalog=7 and IdDirectory=5)你可以比较一下这2个语句的区别。