SQL -在SELECT CASE语句之后合并行

Question

我希望合并具有从SELECT CASE语句返回的匹配ID的行。我在一个pgAdmin 3数据库上使用PG-9.3.4。

这就是我在pgAdmin上要做的：

​

这就是我想要的：

​

我尝试过将它分组，或者将结果加入到表本身，但是不起作用。有什么想法吗？

我的查询代码：

SELECT
    CASE
    WHEN t1.material LIKE '%Refuse%'
        THEN t1.disposal
    ELSE NULL
END AS msw_disp,

    CASE
    WHEN t1.material LIKE '%Met%'
        THEN t1.disposal
    ELSE NULL
END AS mgp_disp,

    CASE
    WHEN t1.material LIKE '%Paper%'
        THEN t1.disposal
    ELSE NULL
END AS pap_disp, t1.district

FROM dsny_net t1

Answer 1

将Aggregate和Group by添加到当前查询应该可以完成任务

SELECT Max(CASE 
             WHEN t1.material LIKE '%Refuse%' THEN t1.disposal 
           END) AS msw_disp, 
       Max(CASE 
             WHEN t1.material LIKE '%Met%' THEN t1.disposal 
           END) AS mgp_disp, 
       Max(CASE 
             WHEN t1.material LIKE '%Paper%' THEN t1.disposal 
           END) AS pap_disp, 
       t1.district 
FROM   dsny_net t1 
GROUP  BY t1.district 

Answer 2

作为替代办法：

select
  district,
  unnest(array_agg(disposal) filter (where material like '%Refuse%')) as msw_disp,
  unnest(array_agg(disposal) filter (where material like '%Met%')) as mgp_disp,
  unnest(array_agg(disposal) filter (where material like '%Paper%')) as pap_disp,
from dsny_net
group by district;

使用max将删除除最大值以外的所有其他值(如果有的话)。

但是，unnest(array_agg(disposal) filter (where material like ...)) as ...可以被max(disposal) filter (where material like ...) as..取代，从而使查询更短。