描述
现有牛客顾客购买信息表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