请大神帮忙看一下,这个题目的是否跑题了?

[_Fong]

我去一家公司的面试题,回到家里之后,发现题目问的好像很含糊,
请大神帮忙参考一下,我是否作对了?
谢谢!

有三张表:
parts
pid int primary key,
pname varchar(20),
color varchar(50)
suppliers
sid int primary key,
sname varchar(20),
address varchar(50)
catalog
sid int,
pid int,
cost float,
primary key(sid, pid)
说明:catalog 数据表记录不同的suppliers提供不同Parts的价格

问题1:找出只供应红色商品的商家

select c.sid
from parts as p, catalog as c
where p.pid=c.pid
group by c.sid
having count(*)=sum(case when p.color='red' then 1 else 0 end);

问题2:找出供应一种红色零件或者一种绿色零件的供应商的sids;并且根据cost降序排序

select c.sid, p.color, c.cost
from parts p , catalog c
where p.pid=c.pid
    and (p.color='red' or p.color='green')
group by c.sid, p.color
having count(color)=1
order by c.cost desc;

问题3:统计不同供应商提供零件的种类数量,结果集降序排列

select sid, count(*) count
from catalog
group by sid
order by count desc;

[ACMAIN_CHM]

题目好像还可以，没有什么特别含糊不清的地方啊。

[_Fong]

引用 1 楼 ACMAIN_CHM 的回复:

题目好像还可以，没有什么特别含糊不清的地方啊。

谢谢大神指点..

[rucypli]

第二题这样比较好吧 select c.sid, p.color, c.cost from parts p , catalog c where p.pid=c.pid and (p.color='red' or p.color='green') group by c.sid having count(distinct color)=1 order by c.cost desc;