牛客-SQL篇-SQL基础进阶-08技能专项-case when使用-技能专项-case when使用-VQ22判断其是否有过购买记录

描述

现有牛客顾客购买信息表customers_info，请查询客户id并新增一列判断该客户是否有过购买记录(`latest_place_order_date` 1为有 0为没有)

示例1

输入：

drop table if exists customers_info;
CREATE TABLE `customers_info` (
`customer_id` int NOT NULL, -- 客户id
`gender` varchar(10) NOT NULL, -- 客户性别
`city` varchar(32) NOT NULL, -- 客户所在城市
`country` varchar(32) NOT NULL, -- 客户所在国家
`age` int, -- 客户年龄
`latest_place_order_date` date ); -- 客户最近购买日期
INSERT INTO customers_info VALUES(18903, 'male', '北京', '中国', 23, '2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66, '2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null, '2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);
INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19, '2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);

复制输出：

customer_id|if_placed_order
18903|1
21089|1
22869|1
16739|0
34992|1
22889|0

代码

SELECT
    customer_id,
    (
        CASE
            WHEN latest_place_order_date IS NOT NULL THEN 1
            ELSE 0
        END
    ) AS if_placed_order
FROM
    customers_info