如何从其他选定查询中进行选择 [英] How can I select from another selected query
问题描述
我收到错误无效的对象名称'ttt'。
什么是正确的查询？
I get an error "Invalid object name 'ttt'".
What's the correct query?
Select
AgencyName,
PerName,
Branch,
(Select SUM(ttt.OfferCount) from ttt where ttt.Satate=5) as Done,
(Select SUM(ttt.OfferCount) from ttt where ttt.State!=5) as NotDone
from
(select TB_Agency.AgencyName, TB_Personel.PerName, TB_Offer.State, count(TB_Offer.Branch) as OfferCount,
TB_Offer.State
from TB_Agency
join TB_Personel on TB_Agency.AgencyID= TB_Personel.AgencyID
join TB_Offer on TB_Personel.PerID = TB_Offer.PerID
GROUP BY TB_Acente.AgencyName, TB_Personel.PerName, TB_Offer.Branch,TB_Offer.State
)
as
ttt
推荐答案
你不能从结果组中选择！
看看你的代码：归结为：
You can't select from the results group!
Look at your code: it boils down to:
SELECT branch, (SELECT COUNT(*) FROM ttt) FROM MyTable AS ttt
因此，为了选择第二项，必须在每种情况下评估所有行。但是为了评估所有行，它首先需要评估第二项。
我不确定你想从数据中得到什么结果，所以我可以;做一个具体的解决方案，但肯定不是它！
你可能需要移动支架内的AS ttt，但是。 ..
So in order to select the second term, it must in each case evaluate all rows. But in order to evaluate all rows, it first needs to evaluate the second term.
I'm not sure exactly what results you want from your data, so I can;t make a concrete solution, but that certainly isn't it!
It's possible that you just need to move the "AS ttt" inside the bracket, but...
我可能会将代码修改为以下内容
I would possibly look at modifying the code to the following
Select
AgencyName,
PerName,
Branch,
(SUM(case when ttt.State = 5 then ttt.OfferCount else 0 end) as Done,
(SUM(case when ttt.State != 5 then ttt.OfferCount else 0 end) as NotDone
from
(select TB_Agency.AgencyName, TB_Personel.PerName, TB_Offer.State, count(TB_Offer.Branch) as OfferCount,
TB_Offer.State
from TB_Agency
join TB_Personel on TB_Agency.AgencyID= TB_Personel.AgencyID
join TB_Offer on TB_Personel.PerID = TB_Offer.PerID
GROUP BY TB_Acente.AgencyName, TB_Personel.PerName, TB_Offer.Branch,TB_Offer.State
)
as
ttt
我能想到做这样一个查询的唯一方法就是使用公用表表达式，但后来我假设您正在运行Sql Server
The only other way I could think of doing such a query would be to use Common Table Expression, but then I have made an assumption that you are running Sql Server
这篇关于如何从其他选定查询中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
