具有多个右侧行的 TSQL 左连接
当您在 TSQL (MSSQL SERVER) 中执行左联接时，如果右侧有多行，是否可以保证哪一行将随查询返回？
我试图用它来利用右表上的排序。
所以
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
我希望根据我的排序检索表中的所有 ColA，以及左连接的 ColA 结果集中的所有第一行。
语言或服务器对此有任何保证吗？
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?
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.
Is there any guarantee made on this by the language or server?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我相信您需要这个...
版本化表的相当常见的查询，需要对最大查询进行内部联接。
表名“Table”没有帮助，我已将其重命名为 T。
I believe you need this...
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.
没那么简单。 LEFT JOIN 返回所有匹配的右侧行。 所以这里关于担保的问题并不真正相关。 您必须使用子查询执行某些操作才能获取所需的单行，在子查询中使用 TOP 1。
Not that simple. The LEFT JOIN returns all matching right-hand rows. So the question on guarantee here is not really relevant. You'd have to do something with a subquery to get the single row you need, using TOP 1 in the subquery.
LEFT JOIN 返回满足任何 WHERE 条件的所有左手行，无论右侧（在连接键上）是否有匹配行。 如果连接键不匹配，右表中的列将返回为 NULL。
A LEFT JOIN returns all left hand rows satisfying any WHERE criteria regardless of whether there is a matching row on the right (on the join key(s)). Columns in the right table will be returned as NULL where there is no match on join key.