postgresql - 在 postgresql 中的语句的情况下如何使用循环或 IF

Question

select distinct
  x.vrgid, x.Weights, x.geom
from
  (select
     -- postcodes.id as postcodeid ,,fishnet.geom as geom
     fishnet.gid  as vrgid,
     fishnet.geom as geom,

     CASE WHEN
       st_intersects(centroids.geom, urban.geom) and counts.nonurbancells != 0
       THEN 0.95 :: numeric / counts.urbancells -------1st case
     WHEN st_intersects(centroids.geom, urban.geom) and counts.nonurbancells = 0
       THEN 1.00 :: numeric / counts.urbancells ---2ndcase
     WHEN Not st_intersects(centroids.geom, urban.geom) = fishnet.gid :: boolean and counts.nonurbancells != 0
       THEN 0.05 :: numeric / counts.nonurbancells ----3rd case
     ELSE 0
     END          AS Weights
   from vrg.urban_nonurban_count_new as counts 
     inner join vrg.gfk_2016_id_5_digit_pcd_areas2013_projected as postcodes on postcodes.id = counts.postid
     right outer join vrg.rdsid_86_quadgrid_centroids as centroids on st_contains(postcodes.geom, centroids.geom)
     left outer join vrg.rdsid86_quadgrid AS fishnet on fishnet.gid = centroids.gid
     right outer join vrg.rdsid86_katrisk_poly_projected as urban on st_intersects(urban.geom, fishnet.geom)
   where postcodes.id = '42395') as x

我的 PL/Pgsql 函数中有几个 case 语句，其中我得到重复的行（具有重复的 vrgid）。下面是查询结果

这里（标记的行）id 为 7192 是第一个 case 语句的结果（参考查询）我想做的是使用循环或 if 条件从结果中删除 vrgid 和相应的权重，一旦第一个 case 语句为真. 所以我不会得到重复的记录。这怎么可能？

可能是我应该在第三个语句中使用一个条件来导致第一个 case 语句中不存在的 vrgids。

标签： postgresql