Re: [SQL ] 關於一個子查詢
※ 引述《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
推
12/23 17:18, , 1F
12/23 17:18, 1F
討論串 (同標題文章)
本文引述了以下文章的的內容：
完整討論串 (本文為第 2 之 2 篇)：