时间戳

首页 > 解决方案 > 在 pgsql 中获得类似枢轴的操作

postgresql - 在 pgsql 中获得类似枢轴的操作

问题描述

我正在尝试根据错误 ID 以及它们何时更改来获取错误的状态。关于使用以下查询：

select b.bug_id,b.creation_ts as issue_reported,ba.added,ba.bug_when
from bugs b
join bugs_activity ba on b.bug_id = ba.bug_id
join fielddefs fd on ba.fieldid = fd.id
where fd.name = 'bug_status'  order by b.bug_id

我得到结果集（子集如下）

bugid         issue_reported            added          bug_when
61            06-06-2019 9:00           Confirmed      06-06-2019 9:00
61            06-06-2019 9:00           In_Progress    06-06-2019 10:00
61            06-06-2019 9:00           Resolved       06-06-2019 15:00
61            06-06-2019 9:00           Verified       06-07-2019 11:00

如何修改我的查询以获得如下结果集：

bugid issue_reported Confirmed      In_Progress    Resolved    Verified  
61    06-06-2019 9:00 06-06-2019 9:00 06-06-2019 10:00 06-06-2019 15:00 06-07-2019 11:00

我的意思是单排。

标签： postgresqlpivot-table

解决方案

在您现有的查询之上，您想要 GROUP BY bug_id, issue_reported，并将行转换为具有这种排列的列：

SELECT bugid,
 issue_reported,
 max(case when added='Confirmed' then bug_when end) AS "Confirmed",
 max(case when added='In_Progress' then bug_when end) AS "In_Progress",
 ... repeat this for each value of "added" to transpose to rows
FROM (
  -- the query from your question as a subquery 
  select b.bug_id,b.creation_ts as issue_reported,ba.added,ba.bug_when
  from bugs b
  join bugs_activity ba on b.bug_id = ba.bug_id
  join fielddefs fd on ba.fieldid = fd.id
  where fd.name = 'bug_status'
) subq
GROUP BY bugid, issue_reported
ORDER BY 1;

推荐阅读