解题思路:
1.找出uid不同的但是买过pro_id相同的商品的用户 [自连接]
select a.uid, b.pro_id from tb_order a join tb_order b on a.pro_id = b.pro_id where a.uid <> b.uid -- 找出uid不同的但是pro_id相同的商品
2.对uid和pro_id进行分组
select a.uid, a.pro_id from tb_order a join tb_order b on a.pro_id = b.pro_id where a.uid <> b.uid -- 找出uid不同的但是买过pro_id相同的商品的用户 group by uid,pro_id
3.在对uid进行分组得到买过两件及以上的用户
select uid from ( select uid from ( select a.uid, a.pro_id from tb_order a join tb_order b on a.pro_id = b.pro_id where a.uid <> b.uid -- 找出uid不同的但是买过pro_id相同的商品的用户 group by uid,pro_id ) tb0 group by uid -- 得到买商品相同数为2件及以上的用户 having count(1) >= 24.再进行自连接得到符合条件的人和商品
select tb1.uid, tb1.pro_id from tb_order tb1 join ( select uid from ( select uid from ( select a.uid, a.pro_id from tb_order a join tb_order b on a.pro_id = b.pro_id where a.uid <> b.uid -- 找出uid不同的但是买过pro_id相同的商品的用户 group by uid,pro_id ) tb0 group by uid -- 得到买商品相同数为2件及以上的用户 having count(1) >= 2 ) tb2 on tb1.uid = tb2.uid
5.在对pro_id进行分区获取商品数是1的商品[使用自查询即可]
最终sql:
select tb3.uid, tb3.pro_id from ( select tb1.uid, tb1.pro_id from tb_order tb1 join ( select uid from ( select uid from ( select a.uid, a.pro_id from tb_order a join tb_order b on a.pro_id = b.pro_id where a.uid <> b.uid -- 找出uid不同的但是买过pro_id相同的商品的用户 group by uid,pro_id ) tb0 group by uid -- 得到买商品相同数为2件及以上的用户 having count(1) >= 2 ) tb2 on tb1.uid = tb2.uid ) tb3 where pro_id in (select pro_id from tb_order group by pro_id having count(1) = 1)