如何在 SQL 查询中添加列，包括。左外连接

Question

我有这个查询：

   SELECT p.ProductName, 
          dt.MaxTimeStamp, 
          p.Responsible
     FROM Product p
LEFT JOIN (SELECT ProductID, MAX(TimeStamp) AS MaxTimeStamp
             FROM StateLog
            WHERE State = 0
         GROUP BY ProductID, State) dt ON p.ProductID = dt.ProductID 
ORDER BY p.ProductName;

它的工作原理是这样的，但现在我也需要选择“状态”。

棘手的部分是，我只想要“状态”为假的最新“时间戳”。 但现在我还需要最新“时间戳”的“状态”。

我尝试了这个：

   SELECT p.ProductName, dt.State, dt.MaxTimeStamp, p.Responsible
     FROM Product p
LEFT JOIN (SELECT ProductID, MAX(TimeStamp) AS MaxTimeStamp, State
             FROM StateLog
            WHERE State = 0
         GROUP BY ProductID, State) dt ON p.ProductID =dt.ProductID 
ORDER BY p.ProductName;

但它不起作用，因为它给了我最新“时间戳”的“状态”。

所以我希望有一些聪明的头脑可以帮助我。我猜这个问题要么非常简单，要么很难解决。

Answer 1

努力破译您正在寻找的内容，但阅读字里行间是否可以总结为：

1）最近的 StateLog.Timestamp，其中 State 为零

2）最近的 StateLog.Timestamp 的状态

在这种情况下，以下内容（相当丑陋）查询可能会起作用。假设您的分组依据中的“状态”列是“状态”的印刷错误，因为它没有返回到任何地方。

SELECT
    p.ProductName
    , sl.State AS StateWithLatestTimeStamp
    , MAX(CASE WHEN dt1.State = 0 THEN dt1.MaxTimeStamp ELSE NULL END) AS LatestStateZeroTimeStamp
FROM
    (
    SELECT
        ProductID
        , State
        , MAX(TimeStamp) AS MaxTimeStamp
    FROM
        StateLog
    GROUP BY
        ProductId
        , State
    ) dt1
INNER JOIN
    StateLog sl
ON  sl.ProductID = dt1.ProductID
INNER JOIN
    Product p
ON  p.ProductID = sl.ProductID
GROUP BY
    p.ProductName
    , sl.State
    , sl.TimeStamp
HAVING
    sl.TimeStamp = MAX(dt1.MaxTimeStamp)

Answer 2

tvanfosson 和 OMG Ponies 的出色格式化工作。

使用 GROUP BY 时，每列都需要：
1. 应用聚合函数，或者
2. 出现在GROUP BY子句中。

我不知道 Status 是什么，但我假设您需要它。
因此，这是您的查询应该如何显示的示例：

   SELECT p.ProductName, dt.State, dt.MaxTimeStamp, p.Responsible
     FROM Product p
LEFT JOIN (SELECT ProductID, Status, 
                  MAX(State) as State, MAX(TimeStamp) AS MaxTimeStamp
             FROM StateLog
            WHERE State = 0
         GROUP BY ProductID, Status) dt ON p.ProductID = dt.ProductID 
ORDER BY p.ProductName;

但是所有这些都是愚蠢的，因为正如评论中提到的，您按 State = 0 进行过滤，因此您的查询不可能返回任何内容除了 0 以外的状态。

Answer 3

 with cte(Productid,TimeStamp,State,Status)  as
(select productid,TimeStamp,State,status,
max(timestamp) over (partition by productid,status) as max1
from statelog
)

 SELECT p.ProductName, 
          dt.MaxTimeStamp, 
          p.Responsible
     FROM Product p
LEFT JOIN(
select productid,max(case when state=0 then TimeStamp else null end) as MaxTimeStamp,
max(case when Timestamp=max1 then state else null end) as MaxState,
from statelog
group by productid,status)
dt ON p.ProductID = dt.ProductID 
ORDER BY p.ProductName;

我想知道您的逻辑是否有点混乱。我看到您正在按状态分组，但您不在任何地方使用状态。
我无法对此进行测试，但如果您发布表创建脚本和数据填充脚本，我们可以快速测试它。