检索价格高于同类产品平均价格的产品（产品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, pricefrom products outerwhere 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) AWHERE STORE.CLASSID = A.CLASSID STORE.VALUE > AVGVALUE ;-- 分析函数实现SELECT *FROM (SELECT ID,CLASSID,NAME,VALUE,AVG(VALUE) OVER(PARTTION BY CLASSID) AVGVALUE FROM STORE) AWHERE 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语句的区别在什么地方呢？