oracle 查询语句

Question

customer_id product_id 
4           1 
3           1 
2           1 
1           1 
4           2 
5           2 
3           2 
1           2 查询出这个表购买了包括1号客户所有产品的其他客户 
customer_id为客户，product_id为产品(oracel)

Answer 1

SELECT K.CUSTOMER_ID
  FROM (SELECT V.CUSTOMER_ID, COUNT(*) CNT1
          FROM (SELECT *
                  FROM CRM.TMP_TEST001 C
                 WHERE C.CUSTOMER_ID IN
                       (SELECT BB.CUSTOMER_ID
                          FROM (SELECT B.CUSTOMER_ID, COUNT(*) CNT
                                  FROM CRM.TMP_TEST001 B
                                 GROUP BY B.CUSTOMER_ID) BB
                         WHERE BB.CNT = (SELECT COUNT(*)
                                           FROM CRM.TMP_TEST001 A
                                          WHERE A.CUSTOMER_ID = 1))
                   AND C.PRODUCT_ID IN
                       (SELECT A.PRODUCT_ID
                          FROM CRM.TMP_TEST001 A
                         WHERE A.CUSTOMER_ID = 1)) V
         GROUP BY V.CUSTOMER_ID) K
 WHERE CNT1 =
       (SELECT COUNT(*) FROM CRM.TMP_TEST001 A WHERE A.CUSTOMER_ID = 1)
   AND K.CUSTOMER_ID <> 1;

Answer 2

select customer_id from table 
where product_id in(select product_id from table 
                    where customer_id = 1)
having count(*)>=(select count(*) from table where customer_id = 1)
group by customer_id;lz已经开过一帖了吧，试试这个

Answer 3

假定customer_id，product_id 唯一指定一条记录
select CUSTOMER_ID from mytab
where product_id in (select product_id from mytab where CUSTOMER_ID=1)
group by CUSTOMER_ID
having count(*)=(select count(*) from mytab where CUSTOMER_ID=1)

Answer 4

select CUSTOMER_ID from mytab
where product_id in (select product_id from mytab where CUSTOMER_ID=1)
group by CUSTOMER_ID
having count(*)>=(select count(*) from mytab where CUSTOMER_ID=1)

Answer 5

select * from tb t1 where t1.customer_id=1
union 
select * from tb t2  exists(select 1 from tb t3 where t3.customer_id=1 and t3.product_id=t2.product_id)

Answer 6

select t1.customer_id from tb t1 where t1.customer_id=1
union 
select t2.customer_id from tb t2  exists(select 1 from tb t3 where t3.customer_id=1 and t3.product_id=t2.product_id)