select * from table_name where price >(select avg(price) from table_name)
没有表结构,怎么查询啊。 不过Oracle 10g Database SQL开发指南这本书中好像有这个练习。 具体名,记不太清了。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 ); 这个例子是用子查询做的,好像不用子查询也是可以实现的。
有两种解决方案 -- 分组实现 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。 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
上面的语法没什么问题,但还是要考虑,按除价格之外的所有ID号分组 按 产品ID、类型type ,group by 、以及 partition by
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 TB T1,(SELECT ITYPE,AVG(PRICE) AVGPRICE FROM TB group by ITYPE) 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语句的区别在什么地方呢?
不过Oracle 10g Database SQL开发指南这本书中好像有这个练习。
具体名,记不太清了。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 );
这个例子是用子查询做的,好像不用子查询也是可以实现的。
-- 分组实现
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 ;
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
按 产品ID、类型type ,group by 、以及 partition by
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 TB T1,(SELECT ITYPE,AVG(PRICE) AVGPRICE FROM TB group by ITYPE) T2
where t1.itype=t2.itype and ti.price > t2.avgprice;
===
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语句的区别在什么地方呢?
你的第一条SQL是取出price大于所有记录平均值的结果集;
你的第二条SQL是取出price大于相同type的记录平均值的结果集。