时间戳

首页 > 解决方案 > 使用 CASE 语句 SQL 在表中创建新列

sql - 使用 CASE 语句 SQL 在表中创建新列

问题描述

我在 SQL 中有一个名为“full_data”的表，如下所示：

Date         Product    Size   Currency    Paid
10/11/2020   T-shirt    M      EUR         40.00
09/11/2020   Hoodie     S      USD         60.00
etc...

我想使用 CASE 语句在 full_data 中添加一个新列。CASE声明如下：

CASE
    WHEN Product = 'Hoodie' AND Size = 'S' THEN 'Small hoodie'
    WHEN Product = 'Hoodie' AND Size = 'M' THEN 'Medium hoodie'
    WHEN Product = 'T-shirt' AND Size = 'S' THEN 'Small shirt'
    WHEN Product = 'T-shirt' AND Size = 'M' THEN 'Medium shirt'
    ELSE 'Other product'
END AS sale_note

什么是正确的代码，以便最后的表格看起来像：

sale_note       Date         Product    Size   Currency   Paid
Medium shirt    10/11/2020   T-shirt    M      EUR        40.00
Small hoodie    09/11/2020   Hoodie     S      USD        60.00

标签： sqlcaseamazon-redshift

解决方案

您只需将case 表达式放在select：

SELECT (CASE WHEN Product = 'Hoodie' AND Size = 'S' THEN 'Small hoodie'
             WHEN Product = 'Hoodie' AND Size = 'M' THEN 'Medium hoodie'
             WHEN Product = 'T-shirt' AND Size = 'S' THEN 'Small shirt'
             WHEN Product = 'T-shirt' AND Size = 'M' THEN 'Medium shirt'
             ELSE 'Other product'
        END) AS sale_note,
       fd.*
FROM full_data fd;

您可以使用以下信息创建视图：

create view v_full_data as
    SELECT (CASE WHEN Product = 'Hoodie' AND Size = 'S' THEN 'Small hoodie'
                 WHEN Product = 'Hoodie' AND Size = 'M' THEN 'Medium hoodie'
                 WHEN Product = 'T-shirt' AND Size = 'S' THEN 'Small shirt'
                 WHEN Product = 'T-shirt' AND Size = 'M' THEN 'Medium shirt'
                 ELSE 'Other product'
            END) AS sale_note,
           fd.*
    from full_data fd;

我认为 Redshift 不支持生成的列，因此视图是您的最佳选择。

推荐阅读