Oracle中关联子查询

求价格高于同类产品平均价格的产品方法一:分析:每一类产品的平均价格如下:select product_type_id,avg(price) from productsgroup 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 tableproducts_avgasselect product_type_id,avg(price) avg_price from productsgroup by product_type_id求价格高于同类产品平均价格的产品,就可以通过prodducts与products_avg进行关联查询:select product_id,p.product_type_id,p.name,p.pricefrom products pjoin products_avg aon p.product_type_id=a.product_type_idwhere 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,pricefrom products outerwhereprice>(select avg(price)from products innerwhereinner.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);