主页 / dba / 问题 / 214310
Accepted
Mark D
Asked: 2018-08-08 09:10:06 +0800 CST

未来行不存在的地方

  • 772

我有下表

CREATE TABLE TaskJournal (
  TaskJournalId INT NOT NULL AUTO_INCREMENT,
  TaskId INT NOT NULL,
  TaskStatus TINYINT NOT NULL,
  TaskStart INT NOT NULL,
  TaskEnd INT NULL,
  TaskInfo TEXT,
  PRIMARY KEY (TaskJournalId)
) ENGINE = InnoDB;

INSERT INTO TaskJournal (TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo) 
VALUES (2, 1, 1533660028, 1533660500, "No issues present"),
(3, 3, 1533660505, 1533660506, "Exception found: Line 95"),
(2, 1, 1533660015, 1533660018, "No issues present"),
(2, 3, 1533660509, 1533660512, "Exception found: Line 95"),
(2, 1, 1533660515, 1533660530, "No issues present");

问题是这样的，我想收集一份错误列表，但前提是将来没有解决办法。

理想情况下我会得到这样的回报

| TaskJournalId | TaskId | TaskStatus | TaskStart  | TaskEnd    | TaskInfo          |
| 3             | 2      | 1          | 1533660015 | 1533660018 | No issues present |

只是因为将来没有一些 TaskId 存在异常。

所以我想我可以通过一组子查询来做到这一点，如下所示：

SELECT * 
FROM TaskJournal 
WHERE TaskId = 2 
GROUP BY TaskId, TaskStatus 
HAVING TaskStatus=3 
ORDER BY TaskStart DESC

但问题在于它返回第 4 行和第 2 行（按时间降序排列）。但在稍后的某个时候，似乎已经解决了 TaskId=2 而不是 TaskId=3。

任何想法都会有所帮助。

mysql-5.5

3 个回答

  1. MySQL-5.5

    无赖。

    似乎 v5.5 不支持分析函数

    如果是这样，解决方案将类似于：

    select TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
from (
  select  TaskJournalId, TaskId, TaskStatus, TaskStart, TaskEnd, TaskInfo
    ,row_number() over (partition by TaskJournalId,TaskId order by TaskStart DESC) rn
  from TaskJournal
)
where TaskStatus=3 and rn=1;

    我的答案

    升级你的数据库。

    • 1

  2. 看起来您想获取TaskId最后一次出现的每个问题的问题列表，No issues present或者No issues present如果该列表对于某些TaskId. 特殊情况如果没有单个No issues present但有一定的错误TaskId。

    SELECT IFNULL( q.TaskJournalId, x.TaskJournalId ) AS TaskJournalId
       IFNULL( q.TaskId, x.TaskId )               AS TaskId
       IFNULL( q.TaskStatus, x.TaskStatus )       AS TaskStatus
       IFNULL( q.TaskStart, x.TaskStart )         AS TaskStart 
       IFNULL( q.TaskEnd, x.TaskEnd )             AS TaskEnd
       IFNULL( q.TaskInfo, x.TaskInfo )           AS TaskInfo

  FROM ( SELECT DISTINCT TaskId 
           FROM TaskJournal 
       ) AS z

  LEFT JOIN ( SELECT TaskId, MAX(TaskStart) AS maxTS
                FROM TaskJournal 
               WHERE TaskInfo = 'No issues present'
               GROUP BY TaskId
            ) AS w
         ON w.TaskId = z.TaskId

  JOIN TaskJournal  AS x 
         ON x.TaskId = z.TaskId
        AND x.TaskStart = w.maxTS

  LEFT JOIN TaskJournal AS q 
         ON q.TaskId = z.TaskId
        AND ( q.TaskStart > w.maxTS OR w.maxTS IS NULL ) 
;
    • 1
  3. Best Answer

    事实证明这并不像我想象的那么复杂，至少当我退后一步并再次查看问题时是这样。下面是我想出的查询（以及一些小的改进以增强输出）。

      SELECT TaskJournalId, 
         TaskId, 
         TaskStatus, 
         FROM_UNIXTIME(MAX(TaskStart)) AS LastRun, 
         FROM_UNIXTIME(TaskEnd) AS TaskEnd,
         CASE
             WHEN (LENGTH(MAX(TaskInfo)) = LENGTH('No issues present')) THEN 'OK'
             ELSE CONCAT('FAIL:', SUBSTR(MAX(TaskInfo), 17, LENGTH(MAX(TaskInfo)))) 
         END AS TaskInfo
    FROM TaskJournal
GROUP BY TaskId
ORDER BY TaskId;
    • 0

相关问题