為什么使用交叉應用會回傳額外的行。它不應該類似于 INNER JOIN 嗎？我期望的結果是
QuoteID controlNo etc MaxQuoteID COntrolNo
10101 1111 something15 10101 1111
樣本資料：
-- create first example table
drop table if exists #test1
create table #test1 (QuoteID int, controlNo int, etc varchar(100))
insert into #test1 values
(1111, 1111,'something1'),
(10101, 1111,'something15'),
(2222, 2222,'something2'),
(3333, 3333,'something3'),
(3333, 30303,'something35'),
(4444, 4444,'something4')
select * from #test1
--create second example table
drop table if exists #test2
create table #test2 (QuoteID int, ControlNo int)
insert into #test2 values
(1111,1111),
(10101,1111)
select * from #test2
-- resutl query 1. This one works as expected
select *
from #test1 t
inner join (select max(QuoteID) as MaxQuoteID, COntrolNo from #test2 group by ControlNo) tt ON t.QuoteID = tt.MaxQuoteID
-- But why this one doesnt work?
select *
from #test1 t
cross apply
(
-- subquery returns a single quoteid 10101, which is what I need
select max(QuoteID) as QuoteID
from #test2 tt
where tt.QuoteID = t.QuoteID
group by ControlNo
) a
uj5u.com熱心網友回復：
兩個查詢都不相同。
在查詢 1 中，您希望max(QuoteID)分組依據controlNo
在查詢 2 中，您將獲得max(QuoteID)每個controlNo
如果你想要等效的 using CROSS APPLY，應該是
select *
from #test1 t
cross apply
(
select max(tt.QuoteID) as QuoteID, tt.controlNo
from #test2 tt
group by tt.controlNo
having max(QuoteID) = t.QuoteID
) a
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363701.html
標籤：sql-server 查询语句 加入 交叉申请
上一篇：pandas.io.sql.DatabaseError:':在字串格式化PythonpandasSQL錯誤期間并非所有引數都被轉換