代码之家 › 专栏 › 技术社区 › radbyx Matt

如何在SQL查询中添加包含左外联接的列

sql left-join join

1

radbyx Matt · 技术社区 · 15 年前

   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;

它像它应该工作,但现在我需要选择“国家”了。

棘手的是,我只想要“State”为false的最新“TimeStamp”。但是现在我还需要“State”作为最新的“TimeStamp”。

我试过这个:

   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;

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

3 回复 | 直到 15 年前

1

Mark Storey-Smith 15 年前

费尽心思去解读你在寻找什么,但字里行间的阅读可以概括为:

2) 最新状态日志的状态。时间戳

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)

2

0

mechanical_meat nazca 15 年前

伟大的格式化工作由tvanfosson和OMG小马。

GROUP BY 每一列都需要:
1.应用聚合函数,或
分组依据

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 for状态以外的任何内容。

3

0

josephj1989 15 年前

 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;

我想知道你的逻辑是否有点混乱。我看到你是按状态分组的,但你在任何地方都不使用状态。我不能测试出来,但如果你表创建脚本和数据填充脚本后,我们可以很快测试出来。