多个外部联接返回顶部结果

Question

我有一个包含多个左外部连接的查询，为简单起见，我将只包含这两个连接。它看起来像这样：

SELECT Object.ID, Gloss.name, Gloss.order, Title.name from Object
LEFT OUTER JOIN Gloss on Gloss.object_id = Object.ID
LEFT OUTER JOIN Title on Title.object_id = Object.ID

但是，有些项目有多个 Gloss，我只想返回具有最大或最小 Gloss.order 的单行。我的查询的示例输出如下所示：

|Object.ID | Gloss.name | Gloss.order | Title.name
|4.00      | glossvalue1| 1           | TitleValue
|4.00      | glossvalue2| 2           | TitleValue
|3.00      | gloss3-1   | 11          | OtherTitle
|3.00      | gloss3-2   | 13          | OtherTitle
|3.00      | gloss3-3   | 15          | OtherTitle

理想情况下，我想返回如下内容：

|Object.ID | Gloss.name | Gloss.order | Title.name
|4.00      | glossvalue1| 1           | TitleValue
|3.00      | gloss3-1   | 11          | OtherTitle

我想我需要一些最大或最小的东西，但我无法将其与其他外部连接（不需要最大或最小）结合使用。任何帮助不胜感激，如果您需要更多信息，请告诉我。

Answer 1

这应该可以完成这项工作：

SELECT Object.ID, (SELECT name FROM Gloss G WHERE G.object_id = X.object_id AND G.order = X.ord) AS [GlossName],
    X.order, Title.name 
FROM Object
LEFT OUTER JOIN
    (SELECT object_id, MIN(order) ord FROM Gloss GROUP BY object_id) X
ON X.object_id = Object.ID
LEFT OUTER JOIN Title on Title.object_id = Object.ID

我不想取最小光泽度名称，因为我知道您的最小光泽度应该基于光泽顺序。

Answer 2

SELECT Object.ID, min(Gloss.name), min(Gloss.order), Title.name from Object
LEFT OUTER JOIN Gloss on Gloss.object_id = Object.ID
LEFT OUTER JOIN Title on Title.object_id = Object.ID
GROUP BY Object.ID,Title.name

Answer 3

select object.ID, Gloss.name, min(Gloss.order) as order, Title.name
from object
left outer join Gloss on Gloss.object_id = object.ID
left outer join Title on Title.object_id = object.ID
group by object.ID, Gloss.name, Title.name