一道CompleteBook上的SQL题 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>JobHunting - 待字闺中

一道CompleteBook上的SQL题

一道CompleteBook上的SQL题# JobHunting - 待字闺中

Z*42011-02-15 08:02

1 楼

Product(maker,model,type)
PC(model,speed,ram,hd,price)
Laptop(model,speed,ram,hd,screen,price)
Find the average price of PC's and laptops made by manufacturer "D"
要算PC和laptop一起的AVG价格感觉是不是不用嵌套搞不定啊？
我的是
SELECT AVG(Price)
FROM (SELECT maker,Price FROM Product,PC WHERE Product.model=PC.model) AS T1
UNION
(SELECT maker,Price FROM Product,Laptop WHERE Product.model=Laptop.model) AS
T2
GROUP BY maker
HAVING maker=D
对嘛？
有没有简单的啊

x*y2011-02-15 08:02

2 楼

try this:
In mySql:
select (sum(PC.price)+sum(Laptop.price))/(count(PC.price)+count(Laptop.price
))
from
(select model from Product where make = 'D' ) Dmodel
inner join PC
on Dmodel.model = PC.model
inner join Laptop
on Dmodel.modle = Laptop.model

c*t2011-02-15 08:02

3 楼

最简单的方法不能work吗？
select (sum(PC.price)+sum(Laptop.price))/(count(PC.price)+count(Laptop.price
))
from Product, PC, Laptop
where Product.model = PC.model and
Product.model = Laptop.model and
Product.maker = 'D'

B*g2011-02-15 08:02

4 楼

你这个是家庭作业？不要用union，用union all。下面code没测试过：
SELECT AVG(pl.price) FROM
(SELECT p.price FROM PC p, Product p1 WHERE p1.model=p.model AND p1.maker='D
'
UNION ALL
SELECT l.price FROM Laptop l, Product p2 WHERE p2.model=l.model AND p2.maker
='D') pl

T1
AS

【在 Z**********4 的大作中提到】

: Product(maker,model,type)
: PC(model,speed,ram,hd,price)
: Laptop(model,speed,ram,hd,screen,price)
: Find the average price of PC's and laptops made by manufacturer "D"
: 要算PC和laptop一起的AVG价格感觉是不是不用嵌套搞不定啊？
: 我的是
: SELECT AVG(Price)
: FROM (SELECT maker,Price FROM Product,PC WHERE Product.model=PC.model) AS T1
: UNION
: (SELECT maker,Price FROM Product,Laptop WHERE Product.model=Laptop.model) AS

Z*42011-02-15 08:02

5 楼

这个应该不对吧
Product.model = PC.model and
不会同时成立的
price

Z*42011-02-15 08:02

6 楼

不是。。是自己想复习一下随便找个题目做着
union all和union的区别是什么呀

'D
maker

【在 B*****g 的大作中提到】

: 你这个是家庭作业？不要用union，用union all。下面code没测试过：
: SELECT AVG(pl.price) FROM
: (SELECT p.price FROM PC p, Product p1 WHERE p1.model=p.model AND p1.maker='D
: '
: UNION ALL
: SELECT l.price FROM Laptop l, Product p2 WHERE p2.model=l.model AND p2.maker
: ='D') pl
:
: T1
: AS

Z*42011-02-15 08:02

7 楼

没有测试但是如果left join可以这样用的话应该没错的
我总是想着用AVG 其实自己去算也可以呀好idea

price

【在 x***y 的大作中提到】

: try this:
: In mySql:
: select (sum(PC.price)+sum(Laptop.price))/(count(PC.price)+count(Laptop.price
: ))
: from
: (select model from Product where make = 'D' ) Dmodel
: inner join PC
: on Dmodel.model = PC.model
: inner join Laptop
: on Dmodel.modle = Laptop.model

m*d2011-02-15 08:02

8 楼

招聘SQL Developer 网站:
http://jobguideweb.com/it-jobs/sql-developer.html

Product.model=PC.model) AS T1
Product.model=Laptop.model) AS

【在 Z**********4 的大作中提到】