TSQL左连接多个右手行
When you perform a left join in TSQL (MSSQL SERVER) is there any guarantee which row will return with your query if there are multiple rows on the right?
当您在TSQL(MSSQL SERVER)中执行左连接时,如果右侧有多行,是否可以保证哪些行将与您的查询一起返回?
I'm trying to use this to exploit an ordering on the right table.
我正在尝试使用它来利用正确的表格上的排序。
so
Select ColA, ColB, ColC
from T
Left Outer Join
(Select ColA, ColB, ColC
from T--CLARIFIED, this is a self join.
Order by TopColumn Desc) AS OrderedT(ColA, ColB, ColC)
On T.ColA = OrderedT.ColA
I would expect to retrieve all the ColA's in Table, and all the first row in the set of ColA results for my left join based on my ordering.
我希望能够检索表中的所有ColA,并根据我的订单检查左连接的ColA结果集中的所有第一行。
Is there any guarantee made on this by the language or server?
语言或服务器是否对此有任何保证?
3 个解决方案
#1
I believe you need this...
我相信你需要这个......
select T.ColA, T.ColB, T.ColC
from T
inner join
(select ColA, max(TopColumn) MaxTopColumn
from T
group by ColA) OrderedTable
on T.ColA = OrderedTable.ColA and T.TopColumn = OrderedTable.MaxTopColumn
Fairly common query for versioned tables, requires an inner join to a max query.
对版本化表格的常见查询需要内部联接到最大查询。
The table name "Table" doesn't help matters, I've renamed it T.
表名“表”无济于事,我已将其重命名为T.