sql - 修复我的 SQL 查询 View

Question

我有一个我正在尝试编写的查询，该查询显示最近的 状态 ID 来自 Action 表的数据。

这是我的数据库的样子(SQL Server 2008 的截图):



从我的示例数据中，您可以看到 Action 表包含 RequestID 的两 (2) 个条目。 #26.我只想显示最近的 StatusID值(基于 DateStamp 字段)。



我为我的数据库创建了一个 View 。它看起来很糟糕，并且提高了我的 SQL 编写能力。

SELECT
  P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp,
  RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder,
  R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status
FROM Packet AS P
  LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID
  INNER JOIN Action AS A ON A.RequestID = R.ID
  INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID
  INNER JOIN Line AS L ON R.LineID = L.ID
  INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID
  INNER JOIN Status AS S ON A.StatusID = S.ID

但是，此 View 显示 所有 值，我需要它以某种方式只提取任何给定操作的最新行。

我将如何修改我的 View 来做到这一点？

Answer 1

SELECT
  P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp,
  RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder,
  R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status
FROM Packet AS P
  LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID
  INNER JOIN Action AS A ON A.RequestID = R.ID
  INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID
  INNER JOIN Line AS L ON R.LineID = L.ID
  INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID
  INNER JOIN Status AS S ON A.StatusID = S.ID
where A.StatusID = (
    select top 1 StatusID
    from Action
    where RequestID = R.ID
    order by DateStamp desc
    )