sql - SQL中有没有更有效的方法返回最大空间交集的记录？

Question

我有两个 SQL 表。第一个是项目多边形。第二个是我想要基于最大空间交集应用于项目多边形的任何边界。

例如，如果项目 1 与 2 个州重叠，我想返回项目 1 面积最大的州名称。

我已经通过下面的查询完成了此操作。 PrjID 是项目表，STATE 是状态表。我希望根据每个 PrjID 记录和 STATE 记录之间的最大交集返回 statename。

SELECT *
FROM
(SELECT a.PROJECT_DELIVERY_ID, b.statename, a.Shape.STIntersection(b.Shape).STArea() AS Area
    FROM PrjID a
        INNER JOIN STATE b
            ON a.Shape.STIntersects(b.Shape) = 1) as c
INNER JOIN
(SELECT a.PROJECT_DELIVERY_ID, MAX(a.Shape.STIntersection(b.Shape).STArea()) AS Area
    FROM PrjID a
        INNER JOIN STATE b
            ON a.Shape.STIntersects(b.Shape) = 1
    GROUP BY a.PROJECT_DELIVERY_ID) as d
ON c.PROJECT_DELIVERY_ID = d.PROJECT_DELIVERY_ID
    AND c.Area = d.Area

我觉得我运行了两次相同的查询并加入它们。有没有更有效的方法来做到这一点？

谢谢

Answer 1

如果我猜对了，你想要获得最大面积行。您可以使用ROW_NUMBER()。

SELECT * FROM (
    SELECT a.PROJECT_DELIVERY_ID, b.statename, a.Shape.STIntersection(b.Shape).STArea() AS Area, 
        ROW_NUMBER() OVER(PARTITION BY a.PROJECT_DELIVERY_ID ORDER BY a.Shape.STIntersection(b.Shape).STArea() DESC) RN
        FROM PrjID a
            INNER JOIN STATE b
                ON a.Shape.STIntersects(b.Shape) = 1 ) AS T
WHERE RN = 1