- 有訂單事務(wù)表、收藏事務(wù)表,要求：請用一句SQL取出所有用戶對商品的行為特征，特征分為已購買鸳址、
- 購買未收藏、收藏未購買泉懦、收藏且購買
# 數(shù)據(jù)準(zhǔn)備
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;
