关于查询

【1】内联查询
select p.pname, c1_sum1 , c2_sum2
  from p ,
       (select id, sum(q1 ) c1_sum1 from s1 group by id) s1,
       (select id, sum(q2 ) c2_sum2 from s2 group by id) s2
where p.id = s1.id
   and p.id = s2.id
这种类型查询的特点是，在from 与where 之间有新的select 查询，这种查询在执行过程中的结果被存在了临时的视图中所以，也称为内联试图查询。
┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷
┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯
【2】标量子查询
select p.pname,
       (select sum(q1) c1_sum1 from s1 where s1.id = p.id) c1_sum1,
       (select sum(q2) c1_sum1 from s2 where s2.id = p.id) c2_sum2
  from p
这种类型查询的特点是在select 与from 之间有新的select 查询。
Single-row subqueries  return zero rows or one row to the outer SQL statement. There is a special case of a single-row subquery that contains exactly one column; this type of subquery is called a  scalar subquery .
select 后面的查询，是典型的标量查询
┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷
┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯
【3】嵌套子查询
select column1, column2 , column3
  from Table1
where column1 IN (select column1 from Table2)
在where 的语句之后的子查询被称为嵌套子查询。
┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷
┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯
【4】关联子查询
SELECT o1.CustomerID, o1.OrderID , o1.OrderDate
  FROM Orders o1
WHERE o1.OrderDate = ( SELECT Max (OrderDate )
                         FROM Orders o2
                        WHERE o2.CustomerID = o1.CustomerID)
外部查询与内部查询有连接条件的where 后查询被称为关联子查询。
┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷
┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯┯
【5】 with子查询分解
  with
    c1_vw as (select id,sum (q1 ) sum1 from s1 group by id),
    c2_vw as (select id,sum (q2 ) sum2 from s2 group by id),
    c1_c2 as (select t1.id,t1.sum1,t2.sum2 from c1_vw t1,c2_vw t2 where t1.id =t2.id)
select p.pname,sum1 ,sum2
  from p ,c1_c2
where p.id =c1_c2.id
┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷┷