在Postgresql中将两行合并为一行

Question

我有这个表：

CREATE TABLE mytable
(
id integer NOT NULL,
rank integer,
col1 text,
col2 text,
CONSTRAINT mytable_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);

INSERT INTO mytable(id, rank, col1, col2)  VALUES (1, 1, 'c', 'c');
INSERT INTO mytable(id, rank, col1, col2)  VALUES (2, 2, 'a', 'a');
INSERT INTO mytable(id, rank, col1, col2)  VALUES (3, 2, 'b', NULL);
INSERT INTO mytable(id, rank, col1, col2)  VALUES (4, 3, 'c', 'c');

我必须在postgresql9.0中做一个查询，将两行"rank"值相等的行"合并"在一行中，规则如下：对于col1和col2，请选择"id"值较高的值，但如果为NULL，请选择其他值(注意："秩"值相等的行不能超过2(

预期结果：

rank    col1     col2
------------------------------
1   c   c
2   b   a
3   c   c

Answer 1

BTW:rank是一个关键字，我不得不重命名为rnk

---

SELECT a.rnk
, COALESCE(a.col1,b.col1) AS col1
, COALESCE(a.col2,b.col1) AS col2
FROM mytable a
LEFT JOIN mytable b ON a.rnk = b.rnk AND a.id > b.id
WHERE NOT EXISTS (
SELECT * FROM mytable x
WHERE x.rnk = a.rnk
AND x.id > a.id
)
;

---

相同，使用WINDOW功能：

---

SELECT rnk
,COALESCE(col1,lead1) AS col1
,COALESCE(col2,lead2) AS col2
FROM (
SELECT rnk,col1,col2
, rank() OVER (www) AS zrank    -- REVERSED order (per rnk)
, LEAD(col1) OVER www AS lead1  -- Value for next record , if any
, LEAD(col2) OVER www AS lead2  -- Value for next record , if any
FROM mytable
WINDOW www AS (partition BY rnk ORDER BY id DESC)
) xxx
WHERE zrank = 1
;

Answer 2

试试这个：

select mt.rank,
(select col1 from mytable where id = max(case when mt.col1 is not null then mt.id end)) as col1,
(select col2 from mytable where id =max(case when mt.col2 is not null then mt.id end)) as col2
from mytable mt
group by mt.rank 
order by mt.rank; 

使用with和self join的另一种方法

with tab as (select rank, 
max(case when col1 is not null then id end) col1,
max(case when col2 is not null then id end) col2 from mytable
group by rank)
select tab.rank "rank", mt1.col1 "col1", mt2.col2 "col2" 
from tab
left join mytable mt1 on mt1.id=tab.col1 
left join mytable mt2 on mt2.id=tab.col2 
order by tab.rank