题目 : https://www.nowcoder.com/practice/5cddad8995974b0c915ab89961428ee0?tpId=341
数据
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);
需求
查询客户 id , 并加一列 , 判断该客户是否有过购买记录(latest_place_order_date
1 为有 , 0 为没有)
查询结果 :
customer_id|if_placed_order
18903|1
21089|1
解决
case 语法 :
CASE 字段
WHEN 预期值1 THEN 结果1
WHEN 预期值2 THEN 结果2
-- ...
ELSE 结果3
END as 别名
CASE
WHEN 判断1 THEN 结果1
WHEN 判断2 THEN 结果2
-- ...
ELSE 结果3
END as 别名
if判断 :
-- expr : 条件表达式
-- 当为 true,就返回 result_true,否则返回 result_false
if(expr, result_true, result_false)
方法1 :
- 对时间进行俩次判断
select customer_id,
case
when latest_place_order_date is null then 0
when latest_place_order_date is not null then 1
end as if_placed_order
from customers_info;
方法2 :
- 对时间判断一次 , 否则就 else
select customer_id,
case
when latest_place_order_date is null then 0
else 1
end as if_placed_order
from customers_info
方法3 :
select customer_id,
if(latest_place_order_date is null, 0, 1) as if_placed_order
from customers_info;