我去一家公司的面试题,回到家里之后,发现题目问的好像很含糊,
请大神帮忙参考一下,我是否作对了?
谢谢!有三张表:
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;多表查询sql
请大神帮忙参考一下,我是否作对了?
谢谢!有三张表:
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;多表查询sql
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;