首页 > 试题广场 >

有订单事务表orders：有收藏事务表favorites：请

[问答题]
有订单事务表orders：

有收藏事务表favorites：

请用一句SQL取出所有用户对商品的行为特征，特征分为已购买、购买未收藏、收藏未购买、收藏且购买（输出结果如下表）


添加回答
牛客267309741号头像 牛客267309741号
发表于 2020-06-18 12:09:09 回复(4)
耀杨融一头像 耀杨融一
SELECT
a.user_id,a.item_id,
(CASE when a.item_id is not null then 1 else 0 end) as '已购买',
(CASE when a.item_id is not null and b.item_id is null then 1 else 0 end) as '购买未收藏',
(CASE when a.item_id is not null and b.item_id is not null then 1 else 0 end) as '收藏且购买',
'0' as '收藏未购买'
FROM
orders a LEFT JOIN
favorites b ON
a.user_id = b.user_id AND a.item_id = b.item_id
UNION
SELECT
b.user_id,b.item_id,
(CASE when a.item_id is not null then 1 else 0 end) as '已购买',
'0'  as '购买未收藏',
(CASE when a.item_id is not null and b.item_id is not null then 1 else 0 end) as '收藏且购买',
(CASE when a.item_id is null and b.item_id is not null then 1 else 0 end) as '收藏未购买'
FROM
orders a RIGHT JOIN
favorites b ON
a.user_id = b.user_id AND a.item_id = b.item_id
ORDER BY
user_id
编辑于 2020-07-20 16:52:19 回复(0)
新取个名字吧头像 新取个名字吧
购买未收藏为啥都是0？？不是有未收藏但是购买了的记录吗（user_id=003 item_id=203）
发表于 2020-07-18 15:32:58 回复(0)
Dame、CX头像 Dame、CX 


SELECT user_id,
    item_id,
    IF(SUM(is_buy)>0,1,0) AS '已购买',
    IF(SUM(is_buy)>0 AND SUM(is_favor)=0,1,0) AS '购买未收藏',
    IF(SUM(is_buy)=0 AND SUM(is_favor)>0,1,0) AS '收藏未购买',
    IF(SUM(is_buy)>0 AND SUM(is_favor)>0,1,0) AS '收藏且购买'
FROM
  (
  #is_buy中1代表已购买,0代表未购买，is_favor中1代表收藏了,0代表未收藏
  SELECT user_id,item_id,1 AS is_buy,0 AS is_favor 
  FROM orders
  UNION
  SELECT user_id,item_id,0 AS is_buy,1 AS is_favor
  FROM favorites
  ) AS t
GROUP BY user_id,item_id
ORDER BY user_id,item_id
参考答案是错误的
正确答案如下:


编辑于 2021-05-09 16:49:09 回复(0)
牛客752421204号头像 牛客752421204号
使用exists 相关查询

SELECT user_id, item_id, 
1 已购买,
NOT EXISTS(SELECT * FROM favorites f WHERE  f.user_id=o.user_id and f.item_id=o.item_id) 购买未收藏,
0 收藏未购买,
EXISTS(SELECT * FROM favorites f where f.user_id=o.user_id and f.item_id=o.item_id) 购买且收藏
FROM orders o 
UNION
SELECT user_id, item_id,
EXISTS (SELECT  * FROM orders o WHERE o.user_id=f.user_id and o.item_id=f.item_id) 已购买,
0 购买未收藏,
NOT EXISTS (SELECT  * FROM orders o WHERE o.user_id=f.user_id and o.item_id=f.item_id) 收藏未购买,
EXISTS (SELECT  * FROM orders o WHERE o.user_id=f.user_id and o.item_id=f.item_id) 购买且收藏
FROM favorites f
发表于 2021-03-13 16:21:24 回复(0)
Mz0719头像 Mz0719
一个比较讨巧的解法……先把两个表UNION，再加一列status，1代表购买，2代表收藏 

SELECT user_id, 
       item_id, 
       IF(MIN(status) = 1, 1, 0) AS “已购买”,
       IF(MAX(status) = 1, 1, 0) AS “购买未收藏”,
       IF(MIN(status) = 2, 1, 0) AS “收藏未购买”,
       IF(COUNT(DISTINCT status) =2, 1, 0) AS “收藏且购买”
FROM
(SELECT user_id, item_id, 1 AS status FROM orders
UNION ALL
SELECT user_id, item_id, 2 AS status FROM favorites) t
GROUP BY user_id, item_id
ORDER BY user_id, item_id

发表于 2021-02-08 22:30:49 回复(0)
牛客253727663号头像 牛客253727663号
SELECT user_id, item_id,
(case when pay_time is not null then 1 else 0 end) as '已购买‘,
(case when pay_time is not null and fav_time is null then 1 else 0 end) as '购买未收藏’,
(case when pay_time is null and fav_time is not null then 1 else 0 end) as ‘收藏未购买’，
(case when pay_time is not null and fav_time is not null then 1 else 0 end) as '收藏且购买‘
from (orders as o FULL JOIN favorites as f ON
o.user_id = f.user_id AND o.item_id = f.item_id )
order by user_id, item_id
发表于 2020-09-08 13:39:20 回复(0)
舒阿然头像 舒阿然
select 
case when a.user_id is not null then a.user_id else b.user_id end as user_id,
case when a.item_id is not null then a.item_id else b.item_id end as item_id,
case when a.item_id is not null then 1 else 0 end as 已购买，
case when a.item_id is not null and b.item is null then 1 else 0 end as 购买未收藏，
case when a.item_id is null and b.item is not null then 1 else 0 end as 收藏未购买，
case when a.item_id is not null and b.item is not null then 1 else 0 end as 收藏且购买，
from orders a full join favorites b
on a.user_id = b.user_id and a.item_id = b.item_id
这样可行吗？求真的眼神
发表于 2020-07-19 22:02:21 回复(0)
上岸吧兄dei头像 上岸吧兄dei

Oracle支持full join，mysql是不支持full join的，等价于左外连接+ union+右外连接实现 

# 创建表&插入数据
CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`user_id` char(11) NOT NULL,
`item_id` int(11) NOT NULL,
`pay_time` date NOT NULL,
`item_num` int(11) NOT NULL,
PRIMARY KEY (`id`,`user_id`,`item_id`));

INSERT INTO orders VALUES(1,'001',201,'2018-08-31',1);
INSERT INTO orders VALUES(2,'002',203,'1987-06-26',2);
INSERT INTO orders VALUES(3,'003',203,'1996-08-03',1);
INSERT INTO orders VALUES(4,'003',203,'1997-08-03',1);


CREATE TABLE `favorites` (
`id` int(11) NOT NULL,
`user_id` char(11) NOT NULL,
`item_id` int(11) NOT NULL,
`fav_time` date NOT NULL,
PRIMARY KEY (`id`,`user_id`,`item_id`));


INSERT INTO favorites VALUES(1,'001',201,'2018-08-31');
INSERT INTO favorites VALUES(2,'002',202,'1987-06-26');
INSERT INTO favorites VALUES(3,'003',204,'1996-08-03');



# MySQL不支持
SELECT o.user_id, o.item_id, 
(CASE when o.pay_time is not null then 1 else 0 end) as '已购买',
(CASE when o.pay_time is not null and f.fav_time is null then 1 else 0 end) as '购买未收藏',
(CASE when o.pay_time is null and f.fav_time is not null then 1 else 0 end) as '收藏未购买',
(CASE when o.pay_time is not null and f.fav_time is not null then 1 else 0 end) as '收藏且购买'
FROM orders o
FULL OUTER JOIN favorites f
ON o.user_id = f.user_id
AND o.item_id = f.item_id;


# MySQL应该这么写
SELECT o.user_id,o.item_id,
(CASE when o.pay_time is not null then 1 else 0 end) as '已购买',
(CASE when o.pay_time is not null and f.fav_time is null then 1 else 0 end) as '购买未收藏',
(CASE when o.pay_time is null and f.fav_time is not null then 1 else 0 end) as '收藏未购买',
(CASE when o.pay_time is not null and f.fav_time is not null then 1 else 0 end) as '收藏且购买'
FROM orders o 
LEFT JOIN favorites f 
ON o.user_id = f.user_id 
AND o.item_id = f.item_id
UNION
SELECT
f.user_id,f.item_id,
(CASE when o.pay_time is not null then 1 else 0 end) as '已购买',
(CASE when o.pay_time is not null and f.fav_time is null then 1 else 0 end) as '购买未收藏',
(CASE when o.pay_time is null and f.fav_time is not null then 1 else 0 end) as '收藏未购买',
(CASE when o.pay_time is not null and f.fav_time is not null then 1 else 0 end) as '收藏且购买'
FROM orders o 
RIGHT JOIN favorites f 
ON o.user_id = f.user_id 
AND o.item_id = f.item_id
ORDER BY user_id, item_id;

题目答案给的有问题，正确的应该是👇






编辑于 2020-08-26 10:44:13 回复(1)
hahshdbddbxnne头像 hahshdbddbxnne
select 
user_id ,
item_id ,
if(pay_time , 1, 0 ) as 已购买,
if( fav_time and NOT (pay_time ), 1, 0 ) 收藏未购买,
if( pay_time and NOT (fav_time ), 1, 0 ) 购买未收藏,
if( pay_time and fav_time , 1, 0 ) 购买且收藏,

from orders o  join  favorites f 
where o.user_id = f.user_id and o.item_id = f.item_id
发表于 2020-07-22 11:04:18 回复(0)
牛客188000430号头像 牛客188000430号

先以orders表为主表left join 连接两表，再以favorites表为主表right join 连接两表，最后union 去除重复行并合并。 

select o.user_id,o.item_id,
case when o.pay_time is not null then 1 else 0 end as "已购买",
case when o.pay_time is not null and f.fav_time is null then 1 else 0 end as "购买未收藏",
case when o.pay_time is null and f.fav_time is not null then 1 else 0 end as "收藏未购买",
case when o.pay_time is not null and f.fav_time is not null then 1 else 0 end as "收藏且购买"
from orders o left join favorites f on o.user_id=f.user_id,o.item_id=f.item_id 
union
select f.user_id,f.item_id,
case when o.pay_time is not null then 1 else 0 end as "已购买",
case when o.pay_time is not null and f.fav_time is null then 1 else 0 end as "购买未收藏",
case when o.pay_time is null and f.fav_time is not null then 1 else 0 end as "收藏未购买",
case when o.pay_time is not null and f.fav_time is not null then 1 else 0 end as "收藏且购买"
from orders o right join favorites f on o.user_id=f.user_id,o.item_id=f.item_id 
order by user_id,item_id;


发表于 2020-06-18 15:46:17 回复(3)
呐呐呐是locke喔头像 呐呐呐是locke喔 
SELECT o.user_id, o.item_id,
case when o.pay_time is not Null then 1 else 0 end as "已购买",
case when o.pay_time is not NUll and f.fav_time is Null then 1 else 0 end as "购买未收藏",
case when o.pay_time is Null and f.fav_time is not Null then 1 else 0 end as "收藏未购买",
case when o.pay_time is not Null and f.fav_time is not Null then 1 else 0 end as "收藏且购买"
from orders o left join favorites f on o.user_id = f.user_id and o.item_id = f.item_id
UNION 
SELECT f.user_id, f.item_id, 
case when o.pay_time is not Null then 1 else 0 end as "已购买",
case when o.pay_time is not NUll and f.fav_time is Null then 1 else 0 end as "购买未收藏",
case when o.pay_time is Null and f.fav_time is not Null then 1 else 0 end as "收藏未购买",
case when o.pay_time is not Null and f.fav_time is not Null then 1 else 0 end as "收藏且购买"
from orders o right join favorites f on o.user_id = f.user_id and o.item_id = f.item_id
order by user_id, item_id;
商户和物品是联合主键，左右连接分别统计两张表里的情况，最后union去重，排序
编辑于 2020-07-01 17:35:44 回复(0)
喜欢数分的Ray头像 喜欢数分的Ray

不是所有购买的商品都被收藏，所以我们先以左边的order表为主表外关联右表。如果右表记录为空，说明这些items没有被收藏，即购买未收藏

同理，不是所有的items都会被购买，所以以favorites表为主表，外关联orders表。orders表中为空的值就是收藏未购买。

为什么用聚合函数？
因为每个用户可能多次购买同一商品。所以这里采用聚合判断，当然外连接空值判断也一样。 
# 计算第一部分
   (select
    o.user_id,
    o.item_id,
    if(count(o.item_id)>0,1,0) as "已购买",
    case when count(o.item_id)>0 and count(f.item_id)=0 then 1 else 0 end as "购买未收藏",
    case when count(o.item_id)=0 and count(f.item_id)>0 then 1 else 0 end as "收藏未购买",
    case when count(o.item_id)>0 and count(f.item_id)>0 then 1 else 0 end as "购买且收藏"
from orders o
left join favorites f
on o.user_id = f.user_id
and o.item_id = f.item_id
group by o.user_id,o.item_id
order by o.user_id,o.item_id)

# union 去重
union

# 计算第二部分
(select
    f.user_id,
    f.item_id,        
    if(count(o.item_id)>0,1,0) as "已购买",
    case when count(o.item_id)>0 and count(f.item_id)=0 then 1 else 0 end as "购买未收藏",
    case when count(o.item_id)=0 and count(f.item_id)>0 then 1 else 0 end as "收藏未购买",
    case when count(o.item_id)>0 and count(f.item_id)>0 then 1 else 0 end as "购买且收藏"
from favorites f
left join orders o
on f.user_id = o.user_id
and f.item_id = o.item_id
group by f.user_id,f.item_id
order by f.user_id,f.item_id);

最终结果：
图片说明

编辑于 2022-02-19 05:51:24 回复(0)
2htttttttttt头像 2htttttttttt
select 
    o.user_id,
    o.item_id,
    （case when o.pay_time is not null then 1 else 0 end）as '已购买',
    （case when o.pay_time is not null and f.fav_time is null then 1 else 0 end）as '购买未收藏',
    （case when o.pay_time is null and f.fav_time is not null then 1 else 0 end）as '收藏未购买',
    （case when o.pay_time is not null and f.fav_time is not null then 1 else 0 end）as '购买且收藏'
from orders o
left join favorites f
on o.user_id = f.useri_id
and o.item_id = f.item_id 
union
select 
    o.user_id,
    o.item_id,
    （case when o.pay_time is not null then 1 else 0 end）as '已购买',
    （case when o.pay_time is not null and f.fav_time is null then 1 else 0 end）as '购买未收藏',
    （case when o.pay_time is null and f.fav_time is not null then 1 else 0 end）as '收藏未购买',
    （case when o.pay_time is not null and f.fav_time is not null then 1 else 0 end）as '购买且收藏'
from orders o
right join favorites f
on o.user_id = f.useri_id
and o.item_id = f.item_id 
order by o.user_id,o.item_id
发表于 2021-08-26 10:53:17 回复(0)
C2H4头像 C2H4
为啥这么多union？Union不对啊
发表于 2021-02-25 23:15:36 回复(0)
OddPsyche头像 OddPsyche

select ab.user_id,ab.item_id,
(case when ab.pay_time is not null then 1 else 0 end) as '已购买'，
(case when ab.pay_time is not null and ab.fav_time is null then 1 else 0 end) as '购买未收藏',
(case when ab.pay_time is null and ab.fav_time is not null then 1 else 0 end) as '收藏未购买',
(case when ab.pay_time is not null and ab.fav_time is not null then 1 else 0 end) as '收藏且购买'
from
(select *
from orders as a
full join favorites as b
on a.user_id=b.user_id) as ab
group by ab.user_id,order by ab.item_id;


select ab.user_id,ab.item_id,
(case when ab.pay_time is not null then 1 else 0 end) as '已购买'，
(case when ab.pay_time is not null and ab.fav_time is null then 1 else 0 end) as '购买未收藏',
(case when ab.pay_time is null and ab.fav_time is not null then 1 else 0 end) as '收藏未购买',
(case when ab.pay_time is not null and ab.fav_time is not null then 1 else 0 end) as '收藏且购买'
from
(select *
from orders as a
full join favorites as b
on a.user_id=b.user_id) as ab
group by ab.user_id,order by ab.item_id;


编辑于 2020-11-09 19:37:03 回复(0)
牛客407942292号头像 牛客407942292号
select user_id, item_id ,
if(paytime, 1,0) as 已购买，
if(paytime and not fav_time, 1, 0) as 购买未收藏，
if ( fav_time and not paytime, 1, 0) as 收藏未购买，
if (fav_time and paytime, 1, 0) as 收藏且购买
from orders as o inner join favorites f on o.user_id = f.user_id and o.item_id = f.item_id
order by user_id,item_id
发表于 2020-08-20 14:13:29 回复(0)
斐贝头像 斐贝 
select select distinct a.user_id,a.item_id
        ,case when pay_time is not null then 1 else 0 end as 已购买
        ,case when pay_time is not null and fav_time is null then 1 else 0 end as 购买未收藏
        ,case when pay_time is null and  fav_time is not null then 1 else 0 end as 收藏未购买
        ,case when pay_time is not null and fav_time is not null then 1 else 0 end as 收藏且购买
        from (
            select user_id,item_id from orders union (select user_id,item_id from favorites)
        )a
        left join orders as o 
        on a.user_id=o.user_id and a.item_id=o.item_id 
        left join favorites as f on a.user_id=f.user_id and a.item_id=f.item_id 
        order by user_id;

发表于 2020-08-13 20:24:47 回复(0)
伍佰万头像 伍佰万 
SELECT o.user_id,o.item_id,
(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS 'buy',
(CASE WHEN fav_time IS NULL THEN 1 ELSE 0 END) AS 'Nofavbuy',
0 as 'favNobuy',
(CASE WHEN fav_time IS NOT NULL THEN 1 ELSE 0 END) AS 'favbuy'
FROM orders o
LEFT JOIN favorites f
ON o.user_id = f.user_id and o.item_id = f.item_id
UNION
SELECT f.user_id,f.item_id,
(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS 'buy',
0 as 'Nofavbuy',
(CASE WHEN pay_time IS NULL THEN 1 ELSE 0 END) AS 'favNobuy',
(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS 'favbuy'
FROM favorites f
LEFT JOIN orders o
ON f.user_id = o.user_id and f.item_id = o.item_id
ORDER BY user_id,item_id;

发表于 2020-08-09 20:46:34 回复(0)
牛客182266295号头像 牛客182266295号 
SELECT
	a.user_id,a.item_id,
	if(b.user_id is not null,1,0) 已购买,
	if(b.user_id is not null and c.user_id is null,1,0) 购买未收藏,
	if(b.user_id is null and c.user_id is not null,1,0) 收藏未购买,
	if(b.user_id is not null and c.user_id is not null,1,0) 收藏且购买
FROM
	( SELECT user_id,item_id FROM orders UNION SELECT user_id,item_id FROM favorites ) a
	LEFT JOIN orders ON a.user_id = b.user_id AND a.item_id=b.item_id
	LEFT JOIN favorites b ON a.user_id = c.user_id AND a.item_id = c.item_id

发表于 2020-07-10 17:28:15 回复(0)
提交观点

问题信息

小红书 2020 数据分析师
上传者：小小
难度：
20条回答 2373浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号