PostgreSQL - 按组查询 - 复杂查询

Question

这是我的表格TheTable的样子

ColA  | ColB  | 
------+-------+------
abc   | 2005  | 
abc   | 2010  | 
def   | 2009  | 
def   | 2010  | 
def   | 2011  | 
abc   | 2012  | 

我想编写一个查询来返回这个结果:

ColA  | ColB  | ColC
------+-------+------
abc   | 2005  | 2010
def   | 2009  | 2011
abc   | 2012  |  -

- raphloDmelo

---

那么 def | 2011 | 和 abc | 2012 | 呢？ - Ravi Kavaiya

如果您正在尝试实现“SCD类型2”，那么您在问题中展示的数据模型是不完整的，无法支持此类查询。请参阅此链接以获取详细信息：https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2。 - krokodilko

你能解释一下你试图实现什么吗？我看不到任何从给定输入中生成你想要的结果的方法。 - Dmitri

Answer 1

我相信你可以使用窗口函数和嵌套子查询来获得你想要的结果：

select "ColA"
, max(case when parity = 0 then "ColB" end) as "ColB"
, max(case when parity = 1 then "ColB" end) as "ColC"
from (
 select * 
  , (rank() over(partition by "ColA" order by "ColB" asc) - 1)
  , (rank() over(partition by "ColA" order by "ColB" asc) - 1) / 2 as result_row
  , (rank() over(partition by "ColA" order by "ColB" asc) - 1) % 2 as parity

 from TheTable ) t

GROUP BY "ColA", result_row

- George S