作者TeemingVoid (TeemingVoid)
看板Database
标题Re: [SQL ] 关於一个子查询
时间Fri Dec 23 14:51:47 2011
※ 引述《Arim (Arim5566)》之铭言：
: 大家好
: 我是使用sql server 2008
: 想请问一个sql如下
: select "team name"
: from (select player.[Team Name] ,SUM (cast(H as int)) as total from Score
: inner join Player on Score.[Player Name]=Player.[Player Name] inner join List
: on Player.[Team Name]=List.[Team Name] where League='Nations League' group by
: player.[Team Name]) as A
: where A.total=(select MAX(total) from A ) /*不懂为啥系统会说A是无效的物件*/
: 因为我在from的地方已经建立了A
: 可是在where要用子查询查A的时候，系统会说A是无效的物件...
基本上，越内层的括号会越先处理，而你的子查询与後来的select MAX()是在同一阶，
所以会说是无效物件。
解决方式有两个:
A)先将第一个子查询的资料以 select ... into #A from ... 句型汇到 #A 暂存表，
然後再从暂存表 #A 挑出资料
B)应用 Common Table Expressions:
with A as
(
select ...
)
select ... from A
go
-- 「宣告」与「套用」必须在同一个批次(Batch)
以你的例子来说:
with A as
(
select player.[Team Name] ,SUM (cast(H as int)) as total
from Score inner join Player on Score.[Player Name]=Player.[Player Name]
inner join List on Player.[Team Name]=List.[Team Name]
where League='Nations League' group by player.[Team Name]
)
select "team name" from A where total = (select MAX(total) from A )
go
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 114.41.99.110
1F：推 Arim:受教了:) 多谢 12/23 17:18