时间戳

首页 > 解决方案 > SQL - 在两个带有连接的表之间使用 datediff

sql - SQL - 在两个带有连接的表之间使用 datediff

问题描述

我有一个场景，我需要生成一个满足以下要求的报告：

Display status of each support ticket. For each ticket include number of updates, the problem, status, time elapsed between ticket being logged and first update, and the time between ticket being logged and final update.

相关表的数据库架构是：

Ticket (TicketID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID)
TicketUpdate (TicketUpdateID, Message, UpdateTime, TicketID)

到目前为止，我有以下内容：

SELECT t.TicketID,
       COUNT(tu.TicketID) AS 'Number of Updates',
       t.Problem,
       t.Status,
       (
           SELECT DATEDIFF(HOUR, MIN(UpdateTime), MAX(UpdateTime))
           FROM TicketUpdate
           WHERE TicketID = t.TicketID
       ) AS 'Hours bw Q and Last response'
FROM Ticket t
    LEFT JOIN TicketUpdate tu
        ON t.TicketID = tu.TicketID
GROUP BY t.TicketID,
         t.Problem,
         t.Status;

这不能满足要求，但我正在尝试该功能。我期待我能够在 datediff 函数中使用别名，但显然我不能（如果我错了，请纠正我）。

对此问题的任何帮助将不胜感激。谢谢！

标签： sqljoindatediff

解决方案

先聚合更新；

SELECT t.*, tu.NumUpdates,
       DATEDIFF(HOUR, t.LoggedTime, tu.FirstUpdateTime),
       DATEDIFF(HOUR, t.LoggedTime, tu.LastUpdateTime)
FROM Ticket t LEFT JOIN
     (SELECT tu.TicketID, COUNT(*) as NumUpdates,
             MIN(tu.UpdateTime) as firstUpdateTime,
             MAX(tu.UpdateTime) as lastUpdateTime
      FROM TicketUpdate tu
      GROUP BY tu.TicketID
     ) tu
     ON t.TicketID = tu.TicketID

推荐阅读