postgre如何在两个sql中拆分一列

Question

我有一个带有以下列和属性的表舟。

+-----+--------+-------------+|出价|b名称|颜色|+-----+--------+-------------+|88 |索菲|蓝色||17|Kate|浅绿色||13|Peter |黄色||1|Nicola|红色||72|基督|红色||19|Rick|浅绿色||77|Morty|Blue|+-----+--------+--------+----+

我如何打印一个与相同颜色的船只名称匹配的表格，如：

+--------+--------+|船1|船2|+--------+--------+|Sophie|Morty||凯特|里克||尼古拉|基督|+--------+--------+

提前感谢。。

Answer 1

尝试使用self-join，这是DEMO

select 
a.bname as boat1,
b.bname as boat2
from myTable a
join myTable b
on a.color = b.color
where a.bid > b.bid
and a.bname != b.bname

Answer 2

每种颜色只有两艘船：

select color, min(bname) as boat1, max(bname) as boat2
from boat
group by color
having count(*) > 1
order by color;

每种颜色的所有船只，每排两艘：

select b1.color, b1.bname as boat1, b2.bname as boat2
from boat b1
join boat b2 on b2.bname > b1.bname and b2.color = b1.color
order by b1.color;

一排每种颜色的所有船只：

select color, string_agg(bname, ', ' order by bname) as boats
from boat
group by color
having count(*) > 1
order by color;