SQL练习题_用户购买收藏记录合并（拼多多）

拼多多笔试题0805_统计用户数据

笔试题描述

本题来自2018年8月5日拼多多笔试题

• 给定两张表buy和fork分别记录用户的购买记录、收藏记录
• 返回状态“已收藏已购买”“已收藏未购买”“未收藏已购买”，以（0,1）表示

---

表格构建

create table buy(user_id int,item_id int,buy_time DATE);
create table fork(user_id int,item_id int ,fork_time DATE);
insert into buy values(0001,201,'2008-09-04');
insert into buy values(0001,206,'2008-09-04');
insert into buy values(0002,203,'2008-09-04');
insert into buy values(0003,204,'2008-09-04');

insert into fork values(0001,203,'2008-09-04');
insert into fork values(0001,201,'2008-09-04');
insert into fork values(0001,205,'2008-09-04');
insert into fork values(0004,203,'2008-09-04');
insert into fork values(0003,204,'2008-09-04');
insert into fork values(0002,201,'2008-09-04');

表格结果如下：

TABLE buy

TABLE fork

数据观察

• 表格中可以发现如下问题

有些商品已购买，未收藏
有些商品未购买，已收藏

• 最后输出中需要汇总所有用户&商品

---

题目分析

一、合并表格

• 当保证buy表所有数据时，应使用LEFT JOIN
• 若有数据有购买记录，无收藏记录，表格中则会显示NULL

SELECT *
FROM buy LEFT JOIN fork
ON buy.user_id=fork.user_id AND buy.item_id=fork.item_id;

表格结果如下：

---

二、CASE表示（0,1）

• 根据是否为NULL值，进行逻辑判断

CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'

• 当然以buy为主表，是不可能出现【未收藏已购买】【未收藏未购买】的情况的。

最后结果及代码

SELECT buy.user_id,buy.item_id,
    CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
    CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM buy LEFT JOIN fork
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id

---

三、同理复制FORK表

SELECT fork.user_id,fork.item_id,
    CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
    CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM fork LEFT JOIN buy
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id

---

题目解答

• 两个结果合并后，即可得到最终结果
• UNION - 去除重复行合并

SELECT buy.user_id,buy.item_id,
    CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
    CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM buy LEFT JOIN fork
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id
UNION
SELECT fork.user_id,fork.item_id,
    CASE WHEN fork.fork_time is not null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'fork&buy',
    CASE WHEN fork.fork_time is not null and buy.buy_time is null THEN 1 ELSE 0 END AS 'fork&NOT buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is not null THEN 1 ELSE 0 END AS 'NOT fork&buy',
    CASE WHEN fork.fork_time is null and buy.buy_time is null THEN 1 ELSE 0 END AS 'NOT fork& NOT buy'
FROM fork LEFT JOIN buy
ON buy.user_id=fork.user_id and buy.item_id=fork.item_id
ORDER BY user_id,item_id;

【转载请注明】https://www.cnblogs.com/igoslly/p/9428136.html

转载于:https://www.cnblogs.com/igoslly/p/9428136.html