检索价格高于同类产品平均价格的产品(产品ID,类型ID,名称,价格),涉及数据库是Store。
------解决方案--------------------
select * from table_name where price >(select avg(price) from table_name)
------解决方案--------------------
没有表结构,怎么查询啊。
不过Oracle 10g Database SQL开发指南这本书中好像有这个练习。
具体名,记不太清了。
- SQL code
select product_id, product_type_id, name, price from products outer where price > ( select avg( price ) from products inner where inner.product_type_id = outer.product_type_id );
------解决方案--------------------
有两种解决方案
- SQL code
-- 分组实现 SELECT * FROM STORE, (SELECT CLASSID,AVG(VALUE) AVGVALUE FROM STORE GROUP BY CLASSID) A WHERE STORE.CLASSID = A.CLASSID STORE.VALUE > AVGVALUE ; -- 分析函数实现 SELECT * FROM (SELECT ID,CLASSID,NAME,VALUE,AVG(VALUE) OVER(PARTTION BY CLASSID) AVGVALUE FROM STORE) A WHERE A.VALUE > A.AVGVALUE ;
------解决方案--------------------
检索价格高于同类产品平均价格的产品(产品ID,类型type,名称name,价格price),涉及数据库是Store。
- SQL code
SELECT ID, TYPE, NAME, price FROM (SELECT ID, TYPE, NAME, price, AVG (price) OVER (PARTITION BY TYPE) avg_price FROM STORE) WHERE price > avg_price
------解决方案--------------------
- SQL code
select * from tb t1 where price > (select avg(price) from tb t2 where t1.itype=t2.itype); SELECT * FROM TB T1,(SELECT ITYPE,AVG(PRICE) AVGPRICE FROM TB) T2 where t1.itype=t2.itype and ti.price > t2.avgprice;
------解决方案--------------------
select * from table_name where price >(select avg(price) from table_name)
===
select product_id, product_type_id, name, price
from products outer
where price >
( select avg( price )
from products inner
where inner.product_type_id = outer.product_type_id );
这两个sql语句的区别在什么地方呢?