主页 / dba / 问题 / 289221
Accepted
s.k
Asked: 2021-04-04 10:35:12 +0800 CST

通过根据第三个条件（映射）将后缀附加到现有字段来填充新列

  • 772

我想知道是否有比以下更好的解决方案来构造一个新列（place_type_new），方法是place_type根据第三列的值（inhabitant在此处显示的案例）：

  UPDATE places_table SET place_type_new = CASE
      WHEN inhabitant = 0 AND place_type LIKE 'Village' THEN 'Village_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Village' THEN 'Village_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Village' THEN 'Village_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Village' THEN 'Village_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Village' THEN 'Village_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Village' THEN 'Village_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Village' THEN 'Village_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Village' THEN 'Village_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Village' THEN 'Village_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'VillagePart' THEN 'VillagePart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'VillagePart' THEN 'VillagePart_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'Neighborhood' THEN 'Neighborhood_XXXL'
      WHEN inhabitant = 0 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXS'
      WHEN inhabitant = 10 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXS'
      WHEN inhabitant = 20 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XS'
      WHEN inhabitant = 100 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_S'
      WHEN inhabitant = 2000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_M'
      WHEN inhabitant = 5000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_L'
      WHEN inhabitant = 10000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XL'
      WHEN inhabitant = 20000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXL'
      WHEN inhabitant = 50000 AND place_type LIKE 'NeighborhoodPart' THEN 'NeighborhoodPart_XXXL'
      ELSE place_type
  END;

这里Village,VillagePart和是更大的一组地方的一部分（例如还有,Neighborhood等等），我只希望这条规则适用于这 4 个元素，因此我的陈述无处不在。NeighborhoodPartTownCityLIKE

但我觉得这非常丑陋，但它工作得很好。附加到现有字段值places_table以构建新字段值的后缀始终根据此模式place_table_new匹配列中的相同数字：inhabitant

0     : XXXS
10    :  XXS
20    :   XS
100   :    S
2000  :    M
5000  :    L
10000 :   XL
20000 :  XXL
50000 : XXXL

为了实现这一点，用这个映射构建一个中间表会更好吗？

我在 Ubuntu 18.04 上使用 PostgreSQL 12。我还将这里的问题缩小到尽可能简单的范围内，希望不会忘记一些重要的事情。

postgresql pattern-matching

1 个回答

  1. Best Answer

    你可以把它减少到以下

    CREATE TABLE places_table(inhabitant INT,place_type VARCHAR(50),place_type_new VARCHAR(50) )

    iNSERT INTO places_table vaLUES (0,'Village',NULL)
,(50,'NeighborhoodPart',NULL),(10000,'Neighborhood',NULL),(10000,'city',NULL)

    UPDATE places_table SET place_type_new = 
 CASE wHEN place_type IN ('Village','VillagePart','Neighborhood','NeighborhoodPart') THEN
   CASE
      WHEN inhabitant = 0  THEN CONCAT(place_type,'_XXXS')
      WHEN inhabitant <= 10  THEN CONCAT(place_type,'_XXS')
      WHEN inhabitant <= 20  THEN CONCAT(place_type,'_XS')
      WHEN inhabitant <= 100 THEN CONCAT(place_type,'_S')
      WHEN inhabitant <= 2000  THEN CONCAT(place_type,'_M')
      WHEN inhabitant <= 5000  THEN CONCAT(place_type,'_L')
      WHEN inhabitant <= 10000 THEN CONCAT(place_type,'_XL')
      WHEN inhabitant <= 20000  THEN CONCAT(place_type,'_XXL')
      WHEN inhabitant <= 50000  THEN CONCAT(place_type,'_XXXL')
     ELSE place_type
  END
ELSE place_type
END;

    seLECT * FROM places_table

    居民| 地点类型 | place_type_new    
---------: | :--------------- | :-----------------
         0 | 村庄 | 村庄_XXXS      
        50 | 邻里部分 | 邻里Part_S
     10000 | 邻里 | 邻里_XL   
     10000 | 城市| 城市

    db<>在这里摆弄

    • 1

相关问题