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

Question

我在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
    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

Answer 1

您只需将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支持生成的列，所以视图是最好的选择。