postgresql 如何在SQL中使用case语句时设置其他列中值

4nkexdtk 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(124)

我为我的列创建不同的标签，这取决于不同的条件。它看起来像这样：

, CASE
    WHEN phone like '%555%' THEN 1
    ELSE 0
END AS first_label

, CASE
    WHEN phone like '%888%' THEN 1
    ELSE 0
END AS second_label

但我也需要在每行的新列中命名这个新标签，比如lable name = 'first_label'。那么，我如何才能做到这一点，而不复制块再次？我的结果：

SELECT 
, phone    
, CASE
    WHEN phone like '%555%' THEN 1
    ELSE 0
END AS first_label
, CASE
    WHEN phone like '%888%' THEN 1
    ELSE 0
END AS second_label
, 'lable_name' as lable_name

FROM mytable

预期产出：
| 电话|第一标号|第二标号|标签名|
| - -----|- -----|- -----|- -----|
| 555 75 71 1| 1| 0|第一标号|
| 888 94 18 3| 0| 1|第二标号|

postgresql

来源：https://stackoverflow.com/questions/76373837/how-to-set-value-in-other-column-while-using-case-statement-in-sql

1条答案

按热度按时间

lf3rwulv1#

为了避免再次重复表达式，只需从查询结果中选择即可。例如：

WITH labeled AS
(
  SELECT 
    phone, 
    CASE WHEN phone like '%555%' THEN 1 ELSE 0 END AS first_label,
    CASE WHEN phone like '%888%' THEN 1 ELSE 0 END AS second_label
  FROM mytable
)
SELECT
  phone,
  first_label,
  second_label,
  CASE
    WHEN first_label = 1 AND second_label = 1 THEN 'both labels'
    WHEN first_label = 1 AND second_label = 0 THEN 'first_label'
    WHEN first_label = 0 AND second_label = 1 THEN 'second_label'
    ELSE                                           'no label'
  END AS label_names
FROM labeled;

演示：https://dbfiddle.uk/zwDO1N1Q

赞(0）回复(0）举报 2023-06-05

我来回答

postgresql 如何在SQL中使用case语句时设置其他列中值

1条答案

相关问题

热门标签

最新问答