postgres中大小写过多的选择

Question

我的需求是根据postgres值，我需要在itemId查询中选择一个值。所以我是这样做的：

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 (.................);

我可以用其他方式做这件事吗？也就是说我能改进它吗？我如何删除WHEN-THEN？

Answer 1

我猜问题出在案例末尾的in：

SELECT . . .
       END itemname in
--------------------^
from item i

您的特定case表达式很好用；Postgres很容易解析它。

如果您愿意，您可以使用如下代码进行查找：

with lookup as (
     values ( 339, 'Revenue'),
            (  28, 'Revenue'),
            . . .
    )
select
from . . . left join
     lookup l
     on l.itemId = ?.itemId  -- put the table name in for itemId

或者--这是最合理的--在item表中添加一列作为名称。这才是真正属于它的地方。

Answer 2

只需创建一个从item id到itemname的映射的表，并将其加入。

SELECT i.itemId, n.Name
FROM Item i
JOIN ItemNames n ON n.ItemId = i.ItemId;

或者..。只需在Item表中添加一个Name列即可。这可能会更好。