詹姆士的筆記本

Aggregate Function(彙總函數)

• 多筆資料變單筆，若SELECT的欄位清單中包含彙總及非彙總函數所有非彙總函數必須列示於GROUP BY子句中
• Aggreate Function 會排除Null作計算，COUNT(*) 會包含Null，COUNT(欄位)則不包含Null
• 常用的Aggreate Function ：SUM、MAX、MIN、AVG、COUNT

--2007年1月份,各類銷售統計

SELECT c.categoryid, c.categoryname, 
 SUM(od.qty*od.unitprice) AS total
FROM Production.Categories c 
INNER JOIN Production.Products p 
	ON c.categoryid = p.categoryid
INNER JOIN Sales.OrderDetails od
	ON p.productid = od.productid
INNER JOIN Sales.Orders o 
	ON o.orderid = od.orderid
WHERE YEAR(o.orderdate) = 2007 
 AND MONTH(o.orderdate) = 1
GROUP BY c.categoryid, c.categoryname
HAVING SUM(od.qty*od.unitprice)<5000
ORDER BY total;

 

SQL 執行順序

• FROM、JOIN --> WHERE -->GROUP BY -->HAVING-->ORDER BY
• Virtual Table -->Filtering -->Aggregate -->Filtering -->Sorting
• 只有Order By可以使用別名
• Order of execution of a Query

 

DISTINCT

--計算2007年1月份人次及來客數
SELECT COUNT(*) AS count_all,
 COUNT(custid) AS count_custid,
 COUNT(DISTINCT custid) AS count_custid_DISTINCT
FROM Sales.Orders 
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 1;

--2007年1月份每位員工各有幾個客戶(重複的視為一筆)
SELECT e.empid, e.lastname, 
 COUNT(DISTINCT o.custid) AS cust_count
FROM HR.Employees e 
INNER JOIN Sales.Orders o 
	On e.empid = o.empid 
WHERE YEAR(o.orderdate) = 2007
 AND MONTH(o.orderdate) = 1
 GROUP BY e.empid, e.lastname;

--2007年，各月份來客數
SELECT MONTH(orderdate) AS mn ,COUNT(DISTINCT custid) AS cust_count 
FROM Sales.Orders
 WHERE orderdate BETWEEN '2007-1-1' AND '2007-12-31'
GROUP BY MONTH(orderdate)

SubQuery

    A subquery is a SQL query nested inside a larger query.

• Self-Contained Subqueries

               A self-contained subquery is independent of the outer query. ( You can run the subquery on its own)(通常可用JOIN來取代)

• Correlated Subqueries

               The subquery is related to the outer SQL statement.

--------------------------------------------------------------------------------------------------------------------------

• Mulit-Value

  • IN / NOT IN (有風險)​

    --IN
    SELECT empid, lastname, firstname
    FROM HR.Employees 
    WHERE empid IN
    (
    SELECT empid 
    FROM Sales.Orders 
    WHERE orderdate BETWEEN '2007-1-1' AND '2007-1-5'
    );
    
    --NOT IN
    SELECT empid, lastname, firstname
    FROM HR.Employees 
    WHERE empid NOT IN
    (
    SELECT empid 
    FROM Sales.Orders 
    WHERE orderdate BETWEEN '2007-1-1' AND '2007-1-5'
    );

  • 使用JOIN取代

    --IN
    SELECT DISTINCT e.empid, e.lastname, firstname
    FROM HR.Employees e 
    INNER JOIN Sales.Orders o 
    	ON e.empid = o.empid
    WHERE o.orderdate BETWEEN '2007-1-1' AND '2007-1-5';
    
    --NOT IN 
    SELECT e.empid, e.lastname, e.firstname
    FROM HR.Employees e
    LEFT JOIN Sales.Orders o 
    	ON e.empid = o.empid
    	AND o.orderdate BETWEEN '2007-1-1' AND '2007-1-5'
    WHERE o.orderid IS NULL;

  • NOT IN 的問題(當table中有null值時，會出現錯誤)

    CREATE TABLE #tmp(empid int);
    INSERT INTO #tmp(empid)VALUES(1), (2), (4), (8), (NULL);
    
    --Correct
    SELECT e.empid, e.lastname, e.firstname
    FROM HR.Employees e
    WHERE empid IN (SELECT empid FROM #tmp);
    
    
    --No result
    SELECT e.empid, e.lastname, e.firstname
    FROM HR.Employees e
    WHERE empid NOT IN (SELECT empid FROM #tmp);
    
    --Correct
    SELECT e.empid, e.lastname, e.firstname
    FROM HR.Employees e
    WHERE empid NOT IN (SELECT empid FROM #tmp
    WHERE empid IS NOT NULL);

    IN & NOT IN 

    • empid	IN	NOT IN
      1	T	F
      2	T	F
      3	U	U
      4	T	F
      5	U	U
      6	U	U
      7	U	U
      8	T	F
      9	U	U

    • EXISTS：
    1. ​The EXISTS checks the existence of a result of a Subquery.​
    2. A valid EXISTS subquery must contain an outer reference and it must be a correlated Subquery

       SELECT e.empid, e.lastname, e.firstname
       FROM HR.Employees e
       WHERE  EXISTS(SELECT * FROM #tmp
       WHERE empid = e.empid);
       
       SELECT e.empid, e.lastname, e.firstname
       FROM HR.Employees e
       WHERE NOT EXISTS(SELECT * FROM #tmp
       WHERE empid = e.empid);
       
       SELECT e.empid, e.lastname, e.firstname
       FROM HR.Employees e 
       WHERE NOT EXISTS(SELECT * FROM Sales.Orders
       WHERE orderdate BETWEEN '2007-1-1' AND '2007-1-5'
       AND empid = e.empid);
       

• Table

1. 必須是SELECT語法
2. SELECT 每個欄位都要有名稱，且不能重複
3. 可以有SELECT、FROM、JOIN、GROUP BY、HAVING等子句，ORDER BY 子句不被允許，除非有TOP、OFFSET / FETCH 或 FOR XML
4. 必須有別名代表整個子查詢

   SELECT e.empid, e.lastname, 
   o.orderid, o.orderdate
   FROM HR.Employees e 
   LEFT JOIN (SELECT orderid, orderdate,
   empid
   FROM Sales.Orders
   WHERE orderdate BETWEEN '2007-1-1' AND '2007-1-5'
   ) o
   ON e.empid = o.empid;
   
   SELECT e.empid, e.lastname, 
   o.total
   FROM HR.Employees e 
   LEFT JOIN (SELECT o.empid, 
   SUM(od.qty*od.unitprice) AS total
   FROM Sales.Orders o 
   INNER JOIN Sales.OrderDetails od
   	ON o.orderid = od.orderid
   WHERE YEAR(o.orderdate) = 2007
    AND MONTH(o.orderdate) = 1
   GROUP BY o.empid
   ) o ON e.empid = o.empid;

    

UNION

    以下泛指(UNION、UNION ALL、INTERSECT、EXCEPT)

1. UNION前後必須是SELECT語法，且欄位個數要一致，型別可互為轉換(按資料型別優先順序)，CTE遞迴時型別、精度、長度要完全一致。
2. UNION前後的SELECT語法各自可以有JOIN、WHERE、GROUP BY、HAVING等子句，唯ORDER BY必須在最後一起排序(*即使有TOP、OFFSET / FETCH 或 FOR XML也不行)
3. UNION會以單一個SELECT語法中的欄位名稱為欄位名稱
4. INTERSECT執行優先順序高於其他三者

    

CREATE TABLE dbo.T1
(
c1 int,
c2 int
);

CREATE TABLE dbo.T2
(
c3 int,
c4 int
);

INSERT INTO dbo.T1(c1, c2)
VALUES(1, 1), (2, 2), (3, 3);

INSERT INTO dbo.T2(c3, c4)
VALUES(2, 2), (3, 3), (4, 4);

--全部可重複
--1,2,3,2,3,4
SELECT c1, c2 FROM dbo.T1
UNION ALL
SELECT c3, c4 FROM dbo.T2;

--聯集
--1,2,3,4
SELECT c1, c2 FROM dbo.T1
UNION 
SELECT c3, c4 FROM dbo.T2;

--交集
--2,3
SELECT c1, c2 FROM dbo.T1
INTERSECT
SELECT c3, c4 FROM dbo.T2;

--差集
--1
SELECT c1, c2 FROM dbo.T1
EXCEPT
SELECT c3, c4 FROM dbo.T2

    

--按Custormers Supplier各自排序，因為無法各自order by
SELECT companyname, contactname, 1 AS kind
FROM Sales.Customers
UNION 
SELECT companyname, contactname, 2
FROM Production.Suppliers
ORDER BY kind, contactname;