移除重複行，只保留最新的工作日誌條目

Question

我創建了這個查詢，它可以工作。問題是它顯示了每個工作日誌的行。我只需要每個事件的最新工作日誌。

SELECT DISTINCT
`HPD:Help Desk`.`Incident Number` AS Incident_Number,
`HPD:Help Desk`.`Status` AS Status,
`HPD:Help Desk`.`Priority` AS Priority,
`HPD:Help Desk`.`Assigned Group` AS Assigned_Group,
`HPD:Help Desk`.`Assignee` AS Assignee,
`HPD:Help Desk`.`Submit Date` AS Submit_Date,
`HPD:Help Desk`.`Last Modified Date` AS Last_Modified_Date,
`HPD:Help Desk`.`Description` AS Description,
`HPD:Help Desk`.`Submitter` AS Submitter,
`HPD:Search-Worklog`.`Worklog Description` AS Worklog_Description,
`HPD:Search-Worklog`.`Work Log ID` AS Work_Log_ID
FROM
`HPD:Help Desk`
INNER JOIN `HPD:Search-Worklog` on (`HPD:Search-Worklog`.`InstanceId` = `HPD:Help Desk`.`InstanceId`)
WHERE
(`HPD:Help Desk`.`Status` in ('Assigned','Pending','In Progress')
And `HPD:Help Desk`.`Submit Date` >= $__from/1000 and `HPD:Help Desk`.`Submit Date` <= $__to/1000)
ORDER BY
`HPD:Help Desk`.`Submit Date` ASC
LIMIT 20

我嘗試過HPD:Help Desk、HPD:Worklog和HPD:Search-Worklog表單。我還嘗試使用CASE語句只顯示每個事件的最新/最大工作日誌ID，但沒有成功。我確定我漏掉了一些明顯/簡單的東西。

Answer 1

假設使用支援視窗函數的最新版本的MySQL（或其變體），取得「最新」的一個非常有效的方法是使用row_number() over()。在over子句中使用partition by來控制什麼會觸發行編號重置，並使用order by來控制分區中獲得行號1的行。現在，要取得「最新」的需要在worklog表中有一列來實現這個功能- 我假設有一個時間戳列（worklog_entry_date存在於該表中（worklog_entry_date），您需要替換實際確定“最新”的列）。將目前查詢嵌套一層，以便您可以過濾行號為1的情況 - 這些將是「最新」的行：

SELECT
    *
FROM (
    SELECT
          h.`Incident Number` AS Incident_Number
        , h.`Status` AS STATUS
        , h.`Priority` AS Priority
        , h.`Assigned Group` AS Assigned_Group
        , h.`Assignee` AS Assignee
        , h.`Submit Date` AS Submit_Date
        , h.`Last Modified Date` AS Last_Modified_Date
        , h.`Description` AS Description
        , h.`Submitter` AS Submitter
        , w.`Worklog Description` AS Worklog_Description
        , w.`Work Log ID` AS Work_Log_ID
        , row_number() over(partition by h.`Incident Number`
                            order by w.worklog_entry_date DESC) as rn
    FROM `HPD: HELP Desk` h
    INNER JOIN `HPD: Search - Worklog` w ON w.`InstanceId` = h.`InstanceId`
    WHERE h.`Status` IN ('Assigned', 'Pending', 'In Progress')
        AND h.`Submit Date` >= $_ _from / 1000
        AND h.`Submit Date` <= $_ _to / 1000
    ) d
WHERE d.rn = 1
ORDER BY
      d.`Submit Date` ASC 
LIMIT 20

注意：當行編號存在時，您不需要使用select distinct - 一般情況下盡量避免使用它。此外，請自行使用表別名來簡化程式碼。