如何才能使这个 SQL 查询更简单/更短？

Question

它应该从 SystemTable 和 ProcessHistory 表中最新记录的 LoadStatus 列返回一些字段。关系是一对多：

SELECT ST.[SystemDetailID], ST.[SystemName], LH.LatestLoadStatus
FROM [SystemTable] AS ST
LEFT OUTER JOIN 
    (
     SELECT LHInner.LoadStatus AS LatestLoadStatus, LHInner.SystemDetailID FROM [dbo].[LoadHistory] AS LHInner
        WHERE LHInner.LoadHistoryID in 
        (
            SELECT LatestLoadHisotoryID FROM 
            (
                SELECT MAX(LoadHistoryID) as LatestLoadHisotoryID, SystemDetailID FROM [dbo].[LoadHistory]
                GROUP BY SystemDetailID
            ) l
        )   
    ) AS LH ON ST.SystemDetailID = LH.SystemDetailID

谢谢，

Answer 1

这是每组最大 n 查询。

一种方法

SELECT ST.[SystemDetailID],
       ST.[SystemName],
       LH.LatestLoadStatus
FROM   [SystemTable] AS ST
       OUTER APPLY (SELECT TOP 1 *
                    FROM   [dbo].[LoadHistory] LH
                    WHERE  ST.SystemDetailID = LH.SystemDetailID
                    ORDER  BY LoadHistoryID DESC) LH  

您还可以使用 row_number

WITH LH
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY SystemDetailID 
                                       ORDER BY LoadHistoryID DESC) RN
         FROM   [dbo].[LoadHistory])
SELECT ST.[SystemDetailID],
       ST.[SystemName],
       LH.LatestLoadStatus
FROM   [SystemTable] AS ST
       LEFT JOIN LH
         ON LH.SystemDetailID = ST.SystemDetailID
            AND LH.RN = 1  

Answer 2

SELECT ST.[SystemDetailID], ST.[SystemName], LH.LatestLoadStatus
FROM [SystemTable] AS ST
INNER JOIN [dbo].[LoadHistory] AS LH 
ON ST.SystemDetailID = LH.SystemDetailID
AND LH.LoadHistoryID IN 
                     (SELECT MAX(LoadHistoryID) as LoadHistoryID 
                      FROM [dbo].[LoadHistory]
                      GROUP BY SystemDetailID )