订购了至少一样产品的顾客: select cid from orders group by cid having count(ordno)>1;通过同城市的代理商来定购的顾客: select ord.cid from orders ord,customers cst,agents agt where ord.cid=cst.cid and ord.aid=agt.aid and cst.city=agt.city;检索出订购了至少一样产品且所有的产品都通过同城市的代理商来订购的顾客CID: select a.cid from (select cid from orders group by cid having count(ordno)>1)a, (select ord.cid from orders ord,customers cst,agents agt where ord.cid=cst.cid and ord.aid=agt.aid and cst.city=agt.city)b where a.cid=b.cid
不好意思,写错了 select distinct o.cid from agents a,orders o,customers c where o.cid=c.cid and o.aid=a.aid and c.city=a.city;
多谢各位,已经有答案 select c.cid from customers c where not exists ( select * from products p,orders o where o.pid=p.pid and o.cid=c.cid and o.qty>1 and not exists ( select * from agents a where a.aid=o.aid and a.city=c.city ) );
select cid from orders group by cid having count(ordno)>1;通过同城市的代理商来定购的顾客:
select ord.cid from orders ord,customers cst,agents agt
where ord.cid=cst.cid and ord.aid=agt.aid and cst.city=agt.city;检索出订购了至少一样产品且所有的产品都通过同城市的代理商来订购的顾客CID:
select a.cid from
(select cid from orders group by cid having count(ordno)>1)a,
(select ord.cid from orders ord,customers cst,agents agt
where ord.cid=cst.cid and ord.aid=agt.aid and cst.city=agt.city)b
where a.cid=b.cid
select distinct o.cid from agents a,orders o,customers c where o.cid=c.cid and o.aid=a.aid and c.city=a.city;
select c.cid from customers c where not exists
(
select * from products p,orders o where o.pid=p.pid and o.cid=c.cid and o.qty>1 and
not exists
(
select * from agents a where a.aid=o.aid and a.city=c.city
)
);