Oracle中关联子查询

求价格高于同类产品平均价格的产品 方法一: 分析:每一类产品的平均价格如下: select product_type_id,avg(price) from products group by product_type_id; PRODUCT_TYPE_ID AVG(PRICE) --------------- ---------- 1 24.975 2 26.22 3 13.24 4 13.99 13.49 如果把上面的结果保存为一个表,如 products_avg, create table products_avg as select product_type_id,avg(price) avg_price from products group by product_type_id 求价格高于同类产品平均价格的产品,就可以通过prodducts与products_avg进行关联查询: select product_id,p.product_type_id,p.name,p.price from products p join products_avg a on p.product_type_id=a.product_type_id where p.price>a.avg_price; PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ---------- 2 1 Chemistry 30 5 2 Z Files 49.99 7 3 Space Force 9 13.49 10 4 Pop 3 15.99 11 4 Creative Yell 14.99 上面的结果正对,即查询到每种产品,看其价格都大于同类产品的平均价格. 方法二: 还可以使用关联子查询实现上面要求. 关联子查询会引用外部查询中的一列或多列.在执行时,外部查询的每一行都被一次一行地传递给子查询.子查询依次读取外部查询传递来的每一值,并将其用到子查询上,直到外部查询所有的行都处理完为止.然后返回查询结果. 本例中,外部查询从products表中检索出所有的行,并将其传递给内部查询.内部查询依次读取外部查询传递来的每一行数据,并对内部查询中product_type_id等于外部查询的中product_type_id值的每种产品计算平均价格. 如:查询第一行product_type_id为1时,把这行数据传递到内部查询,内部查询根据这个编号到产品表中找到同类型的所有行,然后求如平均价格,外部查询再根据这个结果来判断查询条件(外部查询是在执行者where语句时用到子查询,子查询执行完后还返回到where子句中). 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) PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ---------- 2 1 Chemistry 30 5 2 Z Files 49.99 7 3 Space Force 9 13.49 10 4 Pop 3 15.99 11 4 Creative Yell 14.99 例2 : 显示曾经有过两次工作以上的员工. SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id);