在PostgreSQL查询中将代码号转换为项描述

Question

对于每个itemId，我需要在PostgreSQL查询中选择一个值。我是这样做的：

select itemId,
 CASE
-- Revenue
    WHEN itemId in (339,28,29)
    THEN 'Revenues'
    -- Rev CHG calculated on the back-end
--Expenditures
    WHEN itemId = 102
    THEN 'SG'||CHR(38)||'A'
    -- SGA CHG calculated on the back-end
    WHEN itemId = 4375
    THEN 'SG'||CHR(38)||'A MARGIN'
    WHEN itemId = 100
    THEN 'R'||CHR(38)||'D EXPENSE'
    -- RD CHG calculated on the back-end
    WHEN itemId  in (2021, 2114)
    THEN 'CAPEX'
    --CAPEX CHG calculated on the back-end
    WHEN itemId = 34
    THEN 'COGS'
--Income
    WHEN itemId = 379
    THEN 'Net Income'
    WHEN itemId = 7
    THEN 'Earnings from Cont. Ops'
    WHEN itemId = 83
    THEN 'Minority Interest'
    WHEN itemId = 280
    THEN 'Preferred Stock Dividend'
    -- NI CHG calculated on the back-end
    WHEN itemId = 400
    THEN 'EBIT'
    WHEN itemId = 4197
    THEN 'EBIT CHG 1 YR'
    WHEN itemId = 4210
    THEN 'EBIT CHG 2 YR'
    WHEN itemId = 4223
    THEN 'EBIT CHG 3 YR'
    WHEN itemId = 4236
    THEN 'EBIT CHG 5 YR'
    WHEN itemId = 4249
    THEN 'EBIT CHG 7 YR'
    WHEN itemId = 4262
    THEN 'EBIT CHG 10 YR'
    WHEN itemId = 4051
    THEN 'EBITDA'
    WHEN itemId = 4196
    THEN 'EBITDA CHG 1 YR'
    WHEN itemId = 4209
    THEN 'EBITDA CHG 2 YR'
    WHEN itemId = 4222
    THEN 'EBITDA CHG 3 YR'
    WHEN itemId = 4235
    THEN 'EBITDA CHG 5 YR'
    WHEN itemId = 4248
    THEN 'EBITDA CHG 7 YR'
    WHEN itemId = 4261
    THEN 'EBITDA CHG 10 YR'
    WHEN itemId = 4047
    THEN 'EBITDA MARGIN'
    WHEN itemId = 3064
    THEN 'EPS-BASIC'
    --EPS-BASIC CHG calculated on the back-end
    WHEN itemId = 142
    THEN 'EPS-DIL'
    --EPS-DIL CHG calculated on the back-end
    WHEN itemId = 10
    THEN 'GROSS PROFIT'
    WHEN itemId = 4195
    THEN 'GP CHG 1 YR'
    WHEN itemId = 4208
    THEN 'GP CHG 2 YR'
    WHEN itemId = 4221
    THEN 'GP CHG 3 YR'
    WHEN itemId = 4234
    THEN 'GP CHG 5 YR'
    WHEN itemId = 4247
    THEN 'GP CHG 7 YR'
    WHEN itemId = 4260
    THEN 'GP CHG 10 YR'
    WHEN itemId = 4074
    THEN 'GP MARGIN'
    --GP MARGIN CHG calculated on the back-end
    WHEN itemId in (21,5,356,22)
    THEN 'OP PROFIT'
    WHEN itemId = 373
    THEN 'Total Operating Exp'
    --OP PROFIT CHG calculated on the back-end
--Cash Flow
    WHEN itemId = 2207
    THEN 'Cash Flow'
    --CF CHG calculated on the back-end
    WHEN itemId = 4423
    THEN 'UNLEVERED FREE CASH FLOW'
    WHEN itemId = 4430
    THEN 'UCF CHG 1 YR'
    WHEN itemId = 4431
    THEN 'UCF CHG 2 YR'
    WHEN itemId = 4432
    THEN 'UCF CHG 3 YR'
    WHEN itemId = 4433
    THEN 'UCF CHG 5 YR'
    WHEN itemId = 4434
    THEN 'UCF CHG 7 YR'
    WHEN itemId = 4435
    THEN 'UCF CHG 10 YR'
    WHEN itemId = 2006
    THEN 'CASH OPS'
    WHEN itemId = 2081
    THEN 'Net Cash From Discont. Ops'
    --Cash Ops CHG calculated on the back-end
--Return
    WHEN itemId = 4178
    THEN 'ROA'
    WHEN itemId = 4363
    THEN 'ROC'
    WHEN itemId = 4128
    THEN 'ROE'
--Working Capital
    WHEN itemId = 4030
    THEN 'CURRENT RATIO'
    WHEN itemId = 4121
    THEN 'QUICK RATIO'
    WHEN itemId = 4177
    THEN 'ASSET TURNOVER'
    WHEN itemId = 4082
    THEN 'INVENTORY TURNOVER'
  END itemname in
  from item i
  join itemcode ic on i.itemcode = ic.itemcode
  WHERE ic.dataItemId IN (.................);

我能用更好的方式吗？

Answer 1

您应该将itemId值和任何常量字符串之间的所有映射放在一个单独的表(2列)中，并使用它进行连接。这允许您更新映射(通过更改表内容)，甚至以后无需更改查询。(这并不是PostgreSQL特有的，它将与任何关系数据库管理系统一起工作)