Question

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

有张国家表Country,有字段cid(主键),cname,有张产品表Product,有字段pid(主键),pname,有张中间表CP,字段cid,pid;Country和Product以主键关联,
问:
1.出口所有产品的国家有哪些?
2.一种产品都没有出口的国家有哪些?

Answer 1

给你个思路
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);

Answer 2

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 join
cp b
on a.cid=b.cid) tba
where tba.pid is null
)

Answer 3

1.Select A.Cid From (select cid,count(1) as PCount from CP group by Cid) A,(Select Count(1) as PCount2 from Product group by Pid) B
where a.Pcount=b.Pcount2

2.select * from country where not exists(Select 1 from CP where cp.cid=country.cid)

Answer 4

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

Answer 5

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)