SQL练习--某书第四章 表表达式

20190422更新 着重3-2有空4-2

第一种：派生表

 （就是子查询感觉变成一个表）从这个表from或者join

但是需要特别注意，派生表的orderby一点作用都没有，不能用！！！

能用orderby的情况：点那个orderby和limit一起使用的时候，子表达式可以用

按理说下面代码第二个方式但是确实可以，因为orderby和limit一起～～～

-- cid的为02的前三名
SELECT SUM(t1.s_score)
  FROM (
SELECT s1.s_id,s1.s_score
  FROM Score s1
 WHERE s1.c_id = '02'
   AND (SELECT COUNT(*) FROM Score s2
		 WHERE s1.c_id = s2.c_id
           AND s2.s_score > s1.s_score) < 3) AS t1;

SELECT SUM(t1.s_score)
  FROM (
SELECT s_score
FROM score
 WHERE c_id = '02'
 ORDER BY s_score DESC
 LIMIT 3) t1

 

第二特别注意：

使用派生表的一个最常见情景就是，selelct子句处理之前的查询语句中无法引用select子句分配的列别名，因为selelct执行在他们之后

情景3：多个嵌套引用

---------------------------为了表达方便，可以用with+表名+As的形式

 

# 1-1.
SELECT empid,Max(orderdate) AS maxorderdate
  FROM Sales.Orders
 GROUP BY empid;
  
#1-2.
             
SELECT so2.empid,so2.orderdate,so2.orderid,so2.custid
  FROM Sales.Orders AS so2
  JOIN （
        SELECT empid,Max(orderdate) AS maxorderdate
          FROM Sales.Orders
		 GROUP BY empid）AS so1
	ON so2.empid = so1.empid
   AND so2.orderdate = so1.maxorderdate;

-- 特别注意：为什么第二题不像第一题那样直接一个呢？alter
-- 因为groupby的特性，你在select里面不能出现多于的列

#2-1
SELECT orderid,orderdate,custid,empid,
       ROW_NUMBER() OVER(ORDER BY orderdate,orderid) AS rownum
  FROM Sales.Orders;
  
#2-2
SELECT so.orderid,so.orderdate,so.custid,so.empid,so.rownum
  FROM (
       SELECT orderid,orderdate,custid,empid,
              ROW_NUMBER() OVER(ORDER BY orderdate,orderid) AS rownum
         FROM Sales.Orders
        ) AS so
  WHERE so.rownum BETWEEN 11 AND 20;
  
-- 特别注意，为什么我不这样呢
SELECT orderid,orderdate,custid,empid,
       ROW_NUMBER() OVER(ORDER BY orderdate,orderid) AS rownum
  FROM Sales.Orders
 WHERE so.rownum BETWEEN 11 AND 20;
-- 因为select执行顺序在where之后，所有where里面的rownum无法执行

#3.递归 
WITH EmpsCTE AS
(
SELECT empid,mgrid,firstname,lastname
  FROM HR.Employees
 WHERE empid = 9
 UNION ALL
SELECT P.empid,P.mgrid,P.firstname,P.lastname
  FROM EmpsCTE AS C
  JOIN HR.Employees AS P
    ON C.mgrid = P.empid
    )
SELECT  empid,mgrid,firstname,lastname
  FROM EmpsCTE
#4.1
SELECT so.empid,YEAR(sod.orderdate),SUM(sod.qty) AS qty
  FROM Sales.Orders
  JOIN Sales.OrderDetails
    ON so.orderid = sod.orderid
 GROUP BY empid,YEAR(sod.orderdate);
 
 #4.2
SELECT sv.empid,sv.orderyear,sv.qty,
      SELECT SUM(qty）
		FROM Sales.VempOrders AS sv2
	   WHERE sv1.empid = sv2.empid
         AND sv2.orderyear <= sv1.orderyear)AS runqty
   FROM Sales.VemOrders AS sv
  ORDER BY sv1.empid,sv1.orderyear;
 GROUP BY empid,YEAR(sv.orderdate)

重点1-2，2-2，3-2，4-2