多对多关系中的最大值

Question

我使用的是 SQL Server 2008，有 3 个表：x、y 和 z。 y 的存在是为了在 x 和 z 之间创建多对多关系。

 x      y      z
--     --     --
id    xid     id
      zid   sort

上述所有字段都是int。

我想找到针对任何 x 查找具有最高 sort 的 z 的最佳执行方法（不包括反规范化），并返回所有字段来自所有三个表。

示例数据：

x:   id
     --
      1
      2

y:  xid zid
    --- ---
      1   1
      1   2
      1   3
      2   2

z:  id sort
    -- ----
     1    5
     2   10
     3   25

结果集应为 请

xid zid
--- ---
  1   3
  2   2

注意，如果存在多个具有相同最高 sort 值的 z，那么我仍然只希望每个 x 一行代码>.

另请注意，在我的实际情况中，所有三个表中都有其他字段，我的结果集中将需要这些字段。

Answer 1

一种方法是使用子查询。然而，这仅适用于获取 Z 的 ID。如果您需要 x 和 z 表中的更多/所有列，那么这不是最佳解决方案。

SELECT
    x.id,
    (
        SELECT TOP 1
            z.zid
        FROM
            y
        INNER JOIN
            z
        ON
            z.id = y.zid
        WHERE
            y.xid = x.id
        ORDER BY
            z.sort DESC
    )
FROM
    x

这就是您可以执行此操作并返回所有表中的所有数据的方法。

SELECT
    *
FROM
    x
INNER JOIN
    y
ON
    y.xid = x.id
AND
    y.zid =
(
    SELECT TOP 1
        z2.zid
    FROM
        y y2
    INNER JOIN
        z z2
    ON
        z2.id = y2.zid
    WHERE
        y2.xid = x.id
    ORDER BY
        z2.sort DESC
)
INNER JOIN
    z
ON
    z.id = y.zid

Answer 2

select xid,max(zid) as zid from y
group by xid

Answer 3

select xid, zid /* columns from x; and columns from y or z taken from q */
from (select y.xid, y.zid, /* columns from y or z */
             row_number() over(partition by y.xid order by z.sort desc) r
      from y
           join z on z.id = y.zid
     ) q
     join x on x.id = q.xid
where r = 1