我使用的是MariaDB 10.2.21,我在其他地方都没有看到这种确切的情况,因此我请求帮助。

我有一个History表,其中包含JIRA问题中任何字段的每次更改一个记录:

+----------+---------------+----------+-----------------+---------------------+ | IssueKey | OriginalValue | NewValue |      Field      |     ChangeDate      | +----------+---------------+----------+-----------------+---------------------+ | HRSK-184 | (NULL)        |        2 | Risk Detection  | 2019-10-24 10:57:27 | | HRSK-184 | (NULL)        |        2 | Risk Occurrence | 2019-10-24 10:57:27 | | HRSK-184 | (NULL)        |        2 | Risk Severity   | 2019-10-24 10:57:27 | | HRSK-184 | 2             |        4 | Risk Detection  | 2019-10-25 11:54:07 | | HRSK-184 | 2             |        6 | Risk Detection  | 2019-10-25 11:54:07 | | HRSK-184 | 2             |        3 | Risk Severity   | 2019-10-24 11:54:07 | | HRSK-184 | 6             |        5 | Risk Detection  | 2019-10-26 09:11:01 | +----------+---------------+----------+-----------------+---------------------+ 

每个记录都包含新旧值以及已更改的字段类型(“字段”),当然还包含该更改的相应时间戳。

我想查询时间点状态,以向我提供“风险严重性,风险发生和风险检测”每个字段的最新值组合

结果应该是这样的:

+----------+----------------+-------------------+------------------+----------------------+ | IssueKey | Risk Severity  |  Risk Occurrence  |  Risk Detection  |  ChangeDate          | +----------+----------------+-------------------+------------------+----------------------+ | HRSK-184 | 3              |  2                |  5               |  2019-10-26 09:11:01 | +----------+----------------+-------------------+------------------+----------------------+ 

有任何想法吗? 我被卡住了...预先感谢您的努力!

===============>>#1 票数:1

您冷使用几个内联查询

select      IssueKey,     (         select t1.NewValue          from mytable t1          where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Severity'         order by ChangeDate desc limit 1     ) `Risk Severity`,     (         select t1.NewValue          from mytable t1          where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Occurrence'         order by ChangeDate desc limit 1     ) `Risk Occurrence`,     (         select t1.NewValue          from mytable t1          where t1.IssueKey = t.IssueKey and t1.Field = 'Risk Detection'         order by ChangeDate desc limit 1     ) `Risk Severity`,     max(ChangeDate) ChangeDate from mytable t group by IssueKey 

索引为(IssueKey, Field, ChangeDate, NewValue) ,这应该是一个有效的选择。

DB Fiddle上的演示

IssueKey | Risk Severity | Risk Occurrence | Risk Severity | ChangeDate      :------- | ------------: | --------------: | ------------: | :------------------ HRSK-184 |             3 |               2 |             5 | 2019-10-26 09:11:01

  ask by user2018105 translate from so

===============>>#2 票数:1

MariaDB 10.2引入了一些用于分析查询的Window Functions

其中之一是RANK() OVER (PARTITION BY ...ORDER BY...)函数。

首先,您可以应用它,然后通过Conditional Aggregation进行透视:

SELECT IssueKey,        MAX(CASE WHEN Field = 'Risk Severity'   THEN NewValue END ) AS RiskSeverity,        MAX(CASE WHEN Field = 'Risk Occurrence' THEN NewValue END ) AS RiskOccurrence,        MAX(CASE WHEN Field = 'Risk Detection'  THEN NewValue END ) AS RiskDetection,        MAX(ChangeDate) AS ChangeDate   FROM   (    SELECT RANK() OVER (PARTITION BY IssueKey, Field ORDER BY ChangeDate Desc) rnk,           t.*      FROM mytable t    ) t   WHERE rnk = 1   GROUP BY IssueKey;  IssueKey | RiskSeverity  | RiskOccurrence  | RiskDetection  | ChangeDate      -------- + --------------+-----------------+----------------+-------------------- HRSK-184 |             3 |               2 |              5 | 2019-10-26 09:11:01 

演示版

  ask by user2018105 translate from so

本文未有回复,本站智能推荐: