如何写这道题的SQL语句,题目如下:

来源：百度知道 编辑：UC知道 时间：2024/05/22 17:54:08
有张国家表Country,有字段cid(主键),cname,有张产品表Product,有字段pid(主键),pname,有张中间表CP,字段cid,pid;Country和Product以主键关联,
问:
1.出口所有产品的国家有哪些?
2.一种产品都没有出口的国家有哪些?

1:select * from country where cid in(select cid from cp where count(cid)=(select count(cip) from Produc) group by cid)
2:select * from country where cid in(select cid from cp where count(cid)=0)

给你个思路
1.把产品表product中的pid字段提取出来，暂定为表1，用表cp除以表1得到的cid字段，那就是出口所有产品的国家的cid，再跟表country联系一下，就能得到这些国家的名字

2.select a.cid
from country a
where a.cid not in (select c.cid
from cp c);

huzi01245 正解
思路应该是正解的,但是具体SQL语句估计有问题..应该要分组CP来COUNT吧

sql2005测试结果

1.select * from country
where cid in
(select tba.cid
from
(select
cid ,count(pid) as 'shuliang'
from cp
group by cid) tba,

(select count(*) as 'p_shuliang'
from product) tbb
where tba.shuliang=tbb.p_shuliang)

2.select * from country
where cid in
(
select
tba.cid
from
(select a.cid,a.cname,b.pid
from country a
left joi