Z**********4
发帖数: 528 | 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***y
发帖数: 633 | 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********t
发帖数: 5706 | 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*****g
发帖数: 34098 | 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**********4
发帖数: 528 | 5 这个应该不对吧
Product.model = PC.model and
不会同时成立的
price | Z**********4
发帖数: 528 | 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**********4
发帖数: 528 | 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*******d
发帖数: 40 | 8 招聘SQL Developer 网站:
http://jobguideweb.com/it-jobs/sql-developer.html
Product.model=PC.model) AS T1
Product.model=Laptop.model) 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
|
|
