select
V2.代理商id
from
(select count(*) as customer_num from 顾客表c where city = 'LA' ) V1,
(select
T_O.代理商id ,
count(T_C.*) as customer_num
from
顾客表c T_C
inner join
(select 顾客id,代理商id,count(*) from 定单表o group by 顾客id,代理商id having count(*) > !!1/2!!) T_O
on
T_C.id = T_O.顾客id
where
T_C.city = 'LA'
group by
T_O.代理商id) V2
where V1.customer_num = V2.customer_num
V2.代理商id
from
(select count(*) as customer_num from 顾客表c where city = 'LA' ) V1,
(select
T_O.代理商id ,
count(T_C.*) as customer_num
from
顾客表c T_C
inner join
(select 顾客id,代理商id,count(*) from 定单表o group by 顾客id,代理商id having count(*) > !!1/2!!) T_O
on
T_C.id = T_O.顾客id
where
T_C.city = 'LA'
group by
T_O.代理商id) V2
where V1.customer_num = V2.customer_num
from a
where id in
(
select aid
from o oo
where exists (select *
from c
where city = 'LA'
and id = oo.cid
and (select count(distinct pid)
from o
where cid = oo.cid ) >= 2)
)
(
select aa.代理商id,aa.产品id , sum(case when bb.Id is null then 0 else 1 end) as num from
(
select 代理商id , 产品id, 顾客id from 代理商表 a
cross join (select 顾客id from 顾客表 where City = 'LA' ) b
cross join (select 产品id from 产品表 where 产品类型 = '公共产品') c
) aa
left join
(
select id,代理商id , 产品id , 顾客id su from 定单表 o
join 顾客表 c on c.Id = o.顾客id
join 产品表 p on p.Id = o.产品id
where c.City = 'LA' and p.产品名称 = '公共产品'
) bb on bb.代理商id = aa.代理商id and bb.产品id = aa.产品id and bb.顾客id = aa.顾客id
group by aa.代理商id,aa.产品id
) aaa
where num = (select count(*) from 顾客表 where City = 'LA')这是至少一种公共产品的代理商的id的情况
2种的也好处理,自己想吧
where id in
(select a.id
from 定单表o where c.id in (select id from 顾客表c where city='LA')
and
(select count(distinct pid)
from o
where cid = oo.cid ) >= 2))
可能有错误
那么
select 代理商id,顾客id,count(*) num from 定单表 o
group by 代理商id,顾客id
having count(*)>0 /*最少2种时0变为1*/
这是定单表中显示所有代理商为顾客提供的产品状况
select 代理商id from
(
select 代理商id,顾客id,count(*) num from 定单表 o
group by 代理商id,顾客id
having count(*)>0 /*最少2种时0变为1*/
) bb
group by 代理商id
having count(顾客id)=(select count(*) from 顾客表 where City = 'LA')
完事!!
group by 代理商id,顾客id
having count(*)>0 /*最少2种时0变为1*/
加个地区限制
select 代理商id,顾客id,count(*) num from 定单表 o
join 顾客表 c on c.Id = o.顾客id
where c.city = 'LA'
group by 代理商id,顾客id
having count(*)>0 /*最少2种时0变为1*/
select distinct aid from o o1 where not exists (select 1 from c where city='la' and (select count(distinct pid) from o o2 where o1.aid=o2.aid and c.id=o2.cid and o1.pid=o2.pid)<2)