应该是10000001次。看下面的SQL:select (select count(*) from "News" as a where "a"."NewsID" < "b"."NewsID") as count , * from "News" as b 每次第一个属性值都不一样。但是如何得到我需要的那个SQL,只查询2次的?
select (select count(*) from "News" as a where "a"."NewsID" < "b"."NewsID") as count , * from "News" as b 这个写法两个表有关联,当然不一样了。
对于SQL Server,你这个语句会进行优化,结果就是执行2次。 如果是复杂的子查询,可能是你说的10000001那么多次。不过,在查询分析器中的分析来看,好像它还是进行了优化。我想,你的语句可以改变一下,比如pubs库中进行如下的查询: select (select count(*) from employee as b where a.job_id=b.job_id) as cnt, * from employee as a可改为: select b.cnt, a.* from employee as a left outer join (select job_id, count(*) as cnt from employee group by job_id) as b on a.job_id=b.job_id
SQL SERVER,ORACLE都有自动优化机制,可能只执行两次,但对于一些小型数据库,很难说。 最好是改成以下语句: select NewsCount as count, a.* from "News" as a,(select count(*) as NewsCount from "News") as b;
每次第一个属性值都不一样。但是如何得到我需要的那个SQL,只查询2次的?
效率上不用担心。
这个写法两个表有关联,当然不一样了。
如果是复杂的子查询,可能是你说的10000001那么多次。不过,在查询分析器中的分析来看,好像它还是进行了优化。我想,你的语句可以改变一下,比如pubs库中进行如下的查询:
select (select count(*) from employee as b where a.job_id=b.job_id)
as cnt, *
from employee as a可改为:
select b.cnt, a.*
from employee as a
left outer join
(select job_id, count(*) as cnt from employee group by job_id) as b
on a.job_id=b.job_id
最好是改成以下语句:
select NewsCount as count, a.* from "News" as a,(select count(*) as NewsCount from "News") as b;