美团/得物sql面试题

 解题思路:

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) >= 2

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)