2,507
社区成员




with t as (select 'm1' m, 'c1' c from dual
union select 'm2' m, 'c1' c from dual
union select 'm3' m, 'c1' c from dual
union select 'm4' m, 'c1' c from dual
union select 'm5' m, 'c1' c from dual
union select 'm6' m, 'c1' c from dual
union select 'm1' m, 'c2' c from dual
union select 'm2' m, 'c2' c from dual
union select 'm3' m, 'c2' c from dual
union select 'm4' m, 'c2' c from dual
union select 'm5' m, 'c2' c from dual
union select 'm6' m, 'c2' c from dual
union select 'm1' m, 'c3' c from dual
union select 'm2' m, 'c3' c from dual
union select 'm3' m, 'c3' c from dual
union select 'm4' m, 'c3' c from dual
union select 'm5' m, 'c3' c from dual
union select 'm6' m, 'c3' c from dual
union select 'm1' m, 'c4' c from dual
union select 'm2' m, 'c4' c from dual
union select 'm3' m, 'c4' c from dual
union select 'm4' m, 'c4' c from dual
union select 'm5' m, 'c4' c from dual
union select 'm2', 'c3' from dual
union select 'm9', 'c5' from dual)
select * from t
where exists
(select * from (select * from (select c from t group by c having count(*) >= 5)
full join (select m from t group by m having count(*) > 3) on 1 = 1)
where t.m = m and t.c = c);
create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(2 ,1);
insert into t1 values(3 ,1);
insert into t1 values(4 ,1);
insert into t1 values(5 ,1);
insert into t1 values(1 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,2);
insert into t1 values(4 ,2);
insert into t1 values(5 ,2);
insert into t1 values(1 ,3);
insert into t1 values(2 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,3);
insert into t1 values(5 ,3);
insert into t1 values(1 ,4);
insert into t1 values(2 ,4);
insert into t1 values(3 ,4);
insert into t1 values(4 ,4);
insert into t1 values(5 ,4);
insert into t1 values(4 ,6);
insert into t1 values(5 ,6);
create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(2 ,1);
insert into t1 values(3 ,1);
insert into t1 values(4 ,1);
insert into t1 values(5 ,1);
insert into t1 values(1 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,2);
insert into t1 values(4 ,2);
insert into t1 values(5 ,2);
insert into t1 values(1 ,3);
insert into t1 values(2 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,3);
insert into t1 values(5 ,3);
insert into t1 values(1 ,4);
insert into t1 values(2 ,4);
insert into t1 values(3 ,4);
insert into t1 values(4 ,4);
insert into t1 values(6 ,4);
insert into t1 values(7 ,4);
insert into t1 values(4 ,6);
insert into t1 values(5 ,6);
drop table t1;
create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,4);
insert into t1 values(4 ,4);
select * from
(select storecode from t1 group by personcode, storecode having count(*) >= 5)
where
(select count(*) from
(select storecode from t1 group by personcode, storecode having count(*) >= 5)) >= 3;
create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(2 ,1);
insert into t1 values(3 ,1);
insert into t1 values(4 ,1);
insert into t1 values(5 ,1);
insert into t1 values(1 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,2);
insert into t1 values(4 ,2);
insert into t1 values(5 ,2);
insert into t1 values(1 ,3);
insert into t1 values(2 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,3);
insert into t1 values(5 ,3);
insert into t1 values(1 ,4);
insert into t1 values(2 ,4);
select storecode from
(select storecode from
(select * from t1 group by personcode, storecode)
group by storecode having count(storecode) >= 5)
where
(select count(*) from
(select storecode from
(select * from t1 group by personcode, storecode)
group by storecode having count(storecode) >= 5)) >= 3;
这样?
create table t1 (personcode int ,storecode int)
insert into t1 select 1 ,1
union all select 2 ,1
union all select 3 ,1
union all select 4 ,1
union all select 5 ,1
union all select 2 ,1
union all select 4 ,2
union all select 5 ,3
union all select 6 ,2
select *
from t1 a
where
(select distinct count(personcode) from t1 where a.storecode=storecode )>5