SubQuery总结

SubQuery天天用了,理论上总结一下.SubQuery 分三种
1.Noncorrelated 子查询 最普通的样式.
2.Correlated Subqueries 把父查询的列拉到子查询里面去,头一回cyt教我的时候理解了半天.
3.Inline View 也被当成最普通的样式用了.

然后Noncorrelated 子查询又有三种情况
1.返回一行一列 where price < (select max(price) from goods )
2.返回多行一列 where price>= ALL (select price from goods where type=2)
or where NOT price< ANY(select price from goods where type=2)
最常用的IN其实就是=ANY()
3.返回多行多列 一次返回多列当然就节省了查询时间

          UPDATE monthly_orders 
          SET (tot_orders, max_order_amt) =
              (SELECT COUNT(*), MAX(sale_price)
          FROM cust_order)


          DELETE FROM line_item
          WHERE (order_nbr, part_nbr) IN
                (SELECT order_nbr, part_nbr FROM cust_order c)

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8591181/viewspace-899277/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8591181/viewspace-899277/