SQL练习题-2

题目：现在有用户id和他购买过的产品id，给用户推荐和他相似的用户所有买过的产品id；

推荐的产品需要排除本用户已购买过的，相似用户：曾经购买过2种或者两种以上相同的产品；

user_id	product_id
a	1
a	2
a	3
b	1
b	2
b	4

输出结果：

user_id_1	user_id_2	product_id
a	b	3
b	a	4

with simi_user_id as

(SELECT
a.user_id as a_userid,b.user_id b_userid,count(distinct a.product_id) as product_num
FROM
(SELECT
user_id,
product_id
FROM
user_product_info
 ) a 
 
 left join 
 (
 SELECT
user_id,
product_id
FROM
user_product_info
 ) b on a.product_id=b.product_id
 WHERE
 a.user_id<>b.user_id
 group by 
 a.user_id,b.user_id
 HAVING count(distinct a.product_id)>=2
 ) 

SELECT
distinct
a.a_userid,a.b_userid,b.product_id
from
 (
 SELECT
   distinct a_userid,b_userid
 FROM
  simi_user_id 
 ) a 
 
 left join 
 
 (
 SELECT
  user_id,
   product_id
   FROM
   user_product_info
 )b on a.a_userid=b.user_id 
  
 
 left join 
 
 (
 SELECT
a.user_id as a_userid,
a.product_id as a_productid,
b.user_id as b_userid,
b.product_id as b_productid  
FROM
(SELECT
user_id,
product_id
FROM
user_product_info
 ) a 
 
 left join 
 (
 SELECT
user_id,
product_id
FROM
user_product_info
 ) b on a.product_id=b.product_id
 WHERE
 a.user_id<>b.user_id
 
 ) d on b.user_id=d.a_userid and b.product_id=d.a_productid

  WHERE
 d.a_userid is null