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语句的区别在什么地方呢？