时间戳

首页 > 解决方案 > TSQL从以不同ID连接的表中选择与ID关联的最大值？

sql - TSQL从以不同ID连接的表中选择与ID关联的最大值？

问题描述

我有 3 个结构如下的表：

Shipment
+-------------+--------------------+
| Shipment_ID | Shipment_ID_Master |
+-------------+--------------------+
|        4767 |               4767 |
|       88359 |              28431 |
+-------------+--------------------+

Factory
+------------+-------------+
| Factory_ID | Shipment_ID |
+------------+-------------+
|     338161 |        4767 |
|    1178567 |       88359 |
|    1178568 |       88359 |
+------------+-------------+

Coverage
+------------+-----------+----------+
| Factory_ID | Public_ID | Revision |
+------------+-----------+----------+
|     338161 |      2354 |        2 |
|    1178567 |     32436 |        4 |
|    1178568 |      2354 |        3 |
+------------+-----------+----------+

我正在尝试构建一个视图，该视图仅显示与 Shipment_ID 关联的最大 Public_ID 的行。视图应如下所示：

+-------------+--------------------+------------+-----------+----------+
| Shipment_ID | Shipment_ID_Master | Factory_ID | Public_ID | Revision |
+-------------+--------------------+------------+-----------+----------+
|        4767 |               4767 |     338161 |      2354 |        2 |
|       88359 |              28431 |    1178567 |     32436 |        4 |
+-------------+--------------------+------------+-----------+----------+

我有一个查询可以构建这个视图，但它太慢了。当我的应用程序加入此视图时，查询需要几分钟才能完成执行。这是查询：

SELECT  f.Shipment_ID,
        s.Shipment_ID_Master,
        f.Factory_ID,
        c.Public_ID,
        c.Revision
FROM Coverage c
JOIN Factory f ON c.Factory_ID = f.Factory_ID
JOIN Shipment s ON s.Shipment_ID = f.Shipment_ID
WHERE Public_ID = (
    SELECT MAX(Public_ID)
    FROM Coverage c2
    JOIN Factory f2 ON c2.Factory_ID = f2.Factory_ID
    WHERE f2.Shipment_ID = f.Shipment_ID
)

我认为由于 where 子句中的逻辑，引用此视图非常慢。必须有更好更快的方法来做到这一点。

当 Shipment_ID 未与 Public_ID 存储在同一表中时，如何选择与 Shipment_ID 关联的最大 Public_ID？没有where子句可以做到这一点吗？

标签： sqlsql-servertsql

解决方案

您可以使用它Row_number来获得比给定解决方案更好的性能。

尝试以下操作：

;WITH cte AS
(
    SELECT s.Shipment_ID, s.Shipment_ID_Master, f.Factory_ID, c.Public_ID, c.Revision, row_number() OVER (PARTITION BY s.Shipment_ID ORDER BY c.Public_ID desc) AS rn
    FROM #Shipment s
    JOIN #Factory f ON f.Shipment_ID = s.Shipment_ID
    JOIN #Coverage c ON c.Factory_ID = f.Factory_ID
)
SELECT c.Shipment_ID, c.Shipment_ID_Master, c.Factory_ID, c.Public_ID, c.Revision 
FROM cte c WHERE rn = 1

请在此处查看 db<>fiddle 。

推荐阅读