- 有订单事务表、收藏事务表,要求：请用一句SQL取出所有用户对商品的行为特征，特征分为已购买、
- 购买未收藏、收藏未购买、收藏且购买
# 数据准备
create table redbk_orders(
`id` int(5),
`user_id` varchar(10),
`item_id` int(5),
`par_time` varchar(30),
`item_num` int(5)
);
insert into redbk_orders values(1,'001','201','2018-08-31 00:00:01',1);
insert into redbk_orders values(2,'002','203','2018-09-02 12:00:02',2);
insert into redbk_orders values(3,'003','203','2018-09-01 00:00:01',1);
insert into redbk_orders values(4,'003','203','2018-09-04 09:10:30',1);
create table redbk_favorites(
`id` int(5),
`user_id` varchar(10),
`item_id` INT(5),
`fav_time` varchar(30)
);
insert into redbk_favorites values(1,'001',201,'2018-08-31 00:00:01');
insert into redbk_favorites values(2,'002',202,'2018-09-02 12:00:02');
insert into redbk_favorites values(3,'003',204,'2018-09-01 00:00:01');
select
o.user_id
,o.item_id
,1 as "已购买"
,case when f.item_id is null then 1 else 0 end as "购买未收藏"
,0 as "收藏未购买"
,case when f.item_id is not null then 1 else 0 end as "收藏且购买"
from myemployees.redbk_orders o
left join myemployees.redbk_favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
where o.user_id is not null
union
select
o.user_id
,o.item_id
,case when o.item_id is not null then 1 else 0 end as "已购买"
,0 as "购买未收藏"
,case when o.item_id is null then 1 else 0 end as "收藏未购买"
,case when o.item_id is not null then 1 else 0 end as "收藏且购买"
from myemployees.redbk_favorites f
left join myemployees.redbk_orders o
on o.user_id = f.user_id and o.item_id = f.item_id
where o.user_id is not null;
