select * from tb where customer_id<>'1' and product_id in (select product_id from tb where customer_id ='1')
select cumtomer_id,count(1) from tb a where a.customer_id != 1 group by cumtomer_id having count(1) = ( select count(db.product_id) from tb b where customer_id = 1)
楼上的不对,一句SQL好像弄不出来,等楼下的
你这个自己查一下oracle的树查询方法 select * from tablename start with connect by 的用法,你的要求正好符合这个查询
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;试试这个
先看看能能执行 select ','||wmsys.wm_concat(product_id)||',' b,customer_id,tab1.a from table,(select ','||wmsys.wm_concat(product_id)||',' a from table where product=1) tab1 where product_id<>1 group by customer_id having length(b)-length(replace(b,tab1.a,''))=length(tab1.a);
少量数据测试成功。楼主看看这个,是你想要的吗?? select distinct customer_id from prod a where not exists ( select product_id from prod b where customer_id ='1' and not exists ( select product_id from prod c where a.customer_id=c.customer_id and b.product_id=c.product_id and customer_id !='1' ) )
declare a int := 0; Cursor curpro is select PRODUCT_ID from test08 where CUSTOMER_ID=1; Cursor curcus is select CUSTOMER_ID from test08 where CUSTOMER_ID<>1; begin for curcus1 in curcus loop for curpro1 in curpro loop select count(*) into a from test08 where PRODUCT_ID = curpro1.PRODUCT_ID and CUSTOMER_ID = curcus1.CUSTOMER_ID; if a = 0 then exit; end if; end loop; if a = 0 then exit; else dbms_output.put_line(curcus1.CUSTOMER_ID); end if; end loop; end; /
上面有点小问题 declare a int := 0; Cursor curpro is select distinct PRODUCT_ID from test08 where CUSTOMER_ID=1; Cursor curcus is select distinct CUSTOMER_ID from test08 where CUSTOMER_ID<>1; begin for curcus1 in curcus loop for curpro1 in curpro loop select count(*) into a from test08 where PRODUCT_ID = curpro1.PRODUCT_ID and CUSTOMER_ID = curcus1.CUSTOMER_ID; if a = 0 then exit; end if; end loop; if a <> 0 then dbms_output.put_line(curcus1.CUSTOMER_ID); end if; end loop; end; / 这个应该可以了
select count(db.product_id) from tb b where customer_id = 1)
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;试试这个
select ','||wmsys.wm_concat(product_id)||',' b,customer_id,tab1.a
from table,(select ','||wmsys.wm_concat(product_id)||',' a from table where product=1) tab1
where product_id<>1
group by customer_id
having length(b)-length(replace(b,tab1.a,''))=length(tab1.a);
select distinct customer_id from prod a where
not exists ( select product_id from prod b
where customer_id ='1' and not exists
( select product_id from prod c
where a.customer_id=c.customer_id and b.product_id=c.product_id and customer_id !='1' ) )
a int := 0;
Cursor curpro is
select PRODUCT_ID from test08 where CUSTOMER_ID=1;
Cursor curcus is
select CUSTOMER_ID from test08 where CUSTOMER_ID<>1;
begin
for curcus1 in curcus
loop
for curpro1 in curpro
loop
select count(*) into a from test08 where PRODUCT_ID = curpro1.PRODUCT_ID and CUSTOMER_ID = curcus1.CUSTOMER_ID;
if a = 0 then
exit;
end if;
end loop;
if a = 0 then
exit;
else
dbms_output.put_line(curcus1.CUSTOMER_ID);
end if;
end loop;
end;
/
declare
a int := 0;
Cursor curpro is
select distinct PRODUCT_ID from test08 where CUSTOMER_ID=1;
Cursor curcus is
select distinct CUSTOMER_ID from test08 where CUSTOMER_ID<>1;
begin
for curcus1 in curcus
loop
for curpro1 in curpro
loop
select count(*) into a from test08 where PRODUCT_ID = curpro1.PRODUCT_ID and CUSTOMER_ID = curcus1.CUSTOMER_ID;
if a = 0 then
exit;
end if;
end loop;
if a <> 0 then
dbms_output.put_line(curcus1.CUSTOMER_ID);
end if;
end loop;
end;
/
这个应该可以了
看看吧,如果不能满足你的需要,给我留言。