MySQL 查询用于选择单个 ID 内的下一行数据

Question

数据列是 ID、数据和实际创建日期

这是示例数据。

ID  Data        Actual Create Date
111 Open        2018-02-28
111 Comment     2018-02-28
111 Resolved    2018-03-01
222 Open        2018-03-02
222 Comment     2018-03-03
222 Resolved    2018-03-05

我需要做的是，在单个 ID(例如 111)内，我需要创建一个名为结束日期的列，该列采用下一行的实际创建日期，并将其作为上一个条目的结束日期，除非该条目已解决，然后结束日期是创建日期。使数据如下所示

ID  Data        Actual Create Date      End Date
111 Open        2018-02-28              2018-02-28
111 Comment     2018-02-28              2018-03-01
111 Resolved    2018-03-01              2018-03-01
222 Open        2018-03-02              2018-03-03
222 Comment     2018-03-03              2018-03-05
222 Resolved    2018-03-05              2018-03-05

显然我需要提供到目前为止我根据草莓运行的查询

SELECT a.IssueId AS ID, b.ShortId, a.Title AS Issue_Description, a.Request_Type, a.Site, b.Path, b.`Data`, b.`Actual Create Date`
FROM SIM_FE a
LEFT JOIN SIM_FE_Audit_Data b ON b.IssueId = a.IssueId
WHERE b.`Data` IN ('Open','Comment','Audit','Feasibility','Deep Dive','Scoping','Pending Others','Awaiting Requester Info','Resolved')
AND a.Request_Type LIKE '%Capacity:%'

这为我提供了我需要的所有数据。但没有唯一的键，因为数据是对所发生事件的审计跟踪。 ID 对于项目来说是唯一的，但由于每个项目(ID 列)的Data 中显示多个内容，因此您会获得多个 ID。 Short ID、Issue Description、Request_Type、Site 和 Path 都只是为了对方的数据可视化；它们可以在查询功能方面被排除在外，因为它们同样不是唯一的。

此查询的功能本质上是为流程中的每个步骤创建停止日期。目前，每个步骤只有一个开始日期(实际创建日期)。因此，能够将“下一步”的实际创建日期分配给上一步作为结束日期。我已经尝试以我能想到的各种方式调整建议的答案，但它没有按预期工作。我将基本查询的行数从 255 行增加到超过 143K。

这是我的下一次尝试:

SELECT a.IssueId AS ID, a.Title AS Issue_Description, a.Request_Type, a.Site, b.`Data`, b.`Actual Create Date`, b.`End Date`, b.rank
FROM SIM_FE a
LEFT JOIN (
    SELECT 
        IssueId, 
        `Data`, 
        `Actual Create Date`, 
        @rank:=CASE WHEN @id <> IssueId THEN 0 ELSE @rank+1 END AS rank,
        @id:=IssueId AS IssueId1,
        `End Date`
    FROM
        (SELECT @rank:=-1) r,
        (SELECT @id:= -1) i,
        (SELECT *
         FROM SIM_FE_Audit_Data
         WHERE `Data` IN ('Open','Comment','Audit','Feasibility','Deep Dive','Scoping','Pending Others','Awaiting Requester Info','Resolved')
         ORDER BY IssueId, `Actual Create Date`
        ) s
    INNER JOIN (
        SELECT
            IssueId AS ID2,
            `Actual Create Date` As `End Date`
        FROM
            SIM_FE_Audit_Data
    ) t ON t.ID2 = s.IssueId -1
) b ON b.IssueId = a.IssueId
WHERE Request_Type LIKE '%Capacity:%'

这很奇怪，但基本上结束日期是重复重复的。导致 255 行变为 39814 行。所以，连接肯定是错误的。

希望我正确地完成了这个 SQLFiddle

http://sqlfiddle.com/#!9/788b7/1

Answer 1

您可以执行以下操作:

计算排名以获得每个 ID 的所有行的顺序。

SELECT t.id, t.data, t.create_date,
   @rank:=CASE WHEN @id <> id THEN 0 ELSE @rank+1 END AS rn,
   @id:=id AS idl
FROM
  (SELECT @rank:= -1) r,
  (SELECT @id:= -1) i,
  (SELECT *
   FROM mytable
   ORDER BY id, create_date
  ) t

然后使用唯一列单独连接此表并使用 t1.id = t2.id + 1。这将为您提供除最后一个可以使用 CASE 语句之外的所有行的匹配行获取原始日期。

如果您有任何疑问，请告诉我。

编辑

使用 SQLFiddle 后，这里是更新后的查询，它应该给出与问题中的结果类似的结果。

SELECT r1.id, r1.data, r1.create_date, r2.create_date END_DATE
FROM (SELECT t.id, t.data, t.create_date,
   @rank:=CASE WHEN @id <> id THEN 0 ELSE @rank+1 END AS rank,
   @id:=id AS idl
FROM
  (SELECT @rank:= -1) r,
  (SELECT @id:= -1) i,
  (SELECT *
   FROM Table1
   ORDER BY id, create_date
  ) t) r1 LEFT OUTER JOIN (SELECT t.id, t.data, t.create_date,
   @rank:=CASE WHEN @id <> id THEN 0 ELSE @rank+1 END AS rank,
   @id:=id AS idl
FROM
  (SELECT @rank:= -1) r,
  (SELECT @id:= -1) i,
  (SELECT *
   FROM Table1
   ORDER BY id, create_date
  ) t) r2 ON r1.rank = r2.rank - 1 AND r1.id = r2.id

您可以使用 WITH 并将表与其自身连接两次，而不是对 r1 和 r2 重复相同的子查询。由于某种原因，SQLfiddle 不喜欢它，所以我这样做了。

http://sqlfiddle.com/#!9/ac311/7