【SQL.基礎構建-第三節(3/4)】

標籤：重複   資料   常見錯誤   隨機   sel   品種   tips   計算   存在   

--      Tips：彙總和排序


--    一、對錶進行彙總查詢

--　　1.彙總函式

--　　　　（1）5 個常用函數：

--　　　　　　①COUNT：計算表中的記錄（行）數。

--　　　　　　②SUM：計算表中數值列的資料合計值。

--　　　　　　③AVG：計算表中數值列的資料平均值。

--　　　　　　④MAX：求出表中任意列中資料的最大值。

--　　　　　　⑤MIN：求出表中任意列中資料的最小值。

 

--　　　　（2）彙總：將多行匯總成一行。

--2.計算表中資料的行數　

--樣本
SELECT COUNT(*)        -- *：參數，這裡代表全部列
FROM dbo.Conbio;

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

--3.計算 NULL 以外資料的行數

--　　將 COUNT(*) 的參數改成指定對象的列，就可以得到該列的非 NULL 行數。

SELECT COUNT(Conbio_price2)
FROM dbo.Conbio;

--【備忘】除了 COUNT 函數，其它函數不能將星號作為參數。

--　【備忘】COUNT 函數的結果根據參數的不同而不同。COUNT(*) 會得到包含 NULL 的資料行數，而 COUNT(<列名>) 會得到 NULL 之外的資料行數。

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

--4.計算合計值

select
SUM(Conbio_price1) as sum_Conbio_price1,        --總和
AVG(Conbio_price1) as avg_Conbio_price1,        --平均
MAX(Conbio_price1) as max_Conbio_price1,        --最大值
MIN(Conbio_price1) as min_Conbio_price1         --最小值
from dbo.Conbio;

--【備忘】所有的彙總函式，如果以列名為參數，會無視 NULL 值所在的行。
------------------

SELECT MAX(Conbio_DATE),        --Conbio_DATE 為日期
    MIN(Conbio_date)
FROM dbo.Conbio

--【備忘】MAX/MIN 函數幾乎適用於所有資料類型的列。SUM/AVG 函數只適用於數實值型別的列。

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

--　5.使用彙總函式重複資料刪除值（關鍵字 distinct）

--樣本1：計算去除重複資料後的資料行數

SELECT COUNT(DISTINCT Conbio_varieties)
FROM dbo.conbio;

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

--樣本2：先計算資料行數再重複資料刪除資料的結果

SELECT DISTINCT COUNT(Conbio_Varieties)
FROM dbo.Conbio;

--【備忘】在彙總函式的參數中使用 DISTINCT（樣本1），可以重複資料刪除資料。DISTINCT 不僅限於 COUNT 函數，所有的彙總函式都可以使用。

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

--    二、對錶進行分組

--　　1.GROUP BY 子句

--文法：
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--GROUP BY <列名1>, <列名2>, ...;

--樣本
SELECT conbio_varieties AS ‘商品種類‘,
    COUNT(*) AS ‘數量‘
FROM dbo.conbio
GROUP BY conbio_varieties;

--【備忘】GROUP BY 子句中指定的列稱為“彙總鍵”或“分組列”。

--　　【子句的書寫順序（暫訂）】SELECT --> FROM --> WHERE --> GROUP BY

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

--2.彙總鍵中包含 NULL 的情況

SELECT conbio_price2, COUNT(*)
FROM dbo.conbio
GROUP BY conbio_price2;

--【備忘】彙總鍵中包含 NULL 時，在結果中也會以 NULL 行的形式表現出來。

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

--3.WHERE 對 GROUP BY 執行結果的影響

--文法
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--WHERE <運算式>
--GROUP BY <列名1>, <列名2>, ...

SELECT conbio_price2, COUNT(*)
FROM dbo.conbio
WHERE conbio_varieties = ‘衣服‘
GROUP BY conbio_price2

--這裡是先根據 WHERE 子句指定的條件進行過濾，然後再進行彙總處理。

--　　【執行順序】FROM --> WHERE --> GROUP BY --> SELECT。這裡是執行順序，跟之前的書寫順序是不一樣的。

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

--4.與彙總函式和 GROUP BY 子句有關的常見錯誤

--　（1）易錯：在 SELECT 子句中書寫了多餘的列

--　　　SELECT 子句只能存在以下三種元素：

--　　　　　①常數

--　　　　　②彙總函式

--　　　　　③GROUP BY 子句中指定的列名（即彙總鍵）

--易錯點1

--　　【總結】使用 GROUP BY 子句時，SELECT 子句不能出現彙總鍵之外的列名。

--　　（2）易錯：在 GROUP BY 子句中寫了列的別名　

--回顧之前說的執行順序，SELECT 子句是在 GROUP BY 子句之後執行。所以執行到 GROUP BY 子句時無法識別別名。

--　【總結】GROUP BY 子句不能使用 SELECT 子句中定義的別名。


--　（3）易錯：GROUP BY 子句的結果能排序嗎？

--　【解答】它是隨機的。如果想排序，請使用 ORDER BY 子句。

--　【總結】GROUP BY 子句結果的顯示是無序的。


--（4）易錯：在 WHERE 子句中使用彙總函式
--　　【總結】只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能夠使用彙總函式。

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

--三、為彙總結果指定條件

--　　1.HAVING 子句

--　　WHERE 子句智能指定記錄（行）的條件，而不能用來指定組的條件。

--　　【備忘】HAVING 是 HAVE（擁有）的現在分詞。

--文法：
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--GROUP BY <列名1>, <列名2>, ...
--HAVING <分組結果對應的條件>

--【書寫順序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING

SELECT conbio_varieties, COUNT(*)
FROM dbo.conbio
GROUP BY conbio_varieties
HAVING COUNT(*) = 2

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

--2.HAVING 子句的構成要素

--　　（1）3 要素：

--　　　　①常數

--　　　　②彙總函式

--　　　　③GROUP BY 子句中指定的列名（即彙總鍵）

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

--3.HAVING 與 WHERE

--　有些條件可以寫在 HAVING 子句中，又可以寫在 WHERE 子句中。這些條件就是彙總鍵所對應的條件。

--【建議】雖然結果一樣，彙總鍵對應的條件應該寫在 WHERE 子句中，不是 HAVING 子句中。

--　　【理由】①WHERE 子句的執行速度比 HAVING 快。

--　　　　　　②意義：WHERE 子句 = 指定行所對應的條件，HAVING 子句 = 指定組所對應的條件。

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

--四、對查詢結果進行排序

--1.ORDER BY 子句

--文法：
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--ORDER BY <排序基準列1>, <排序基準列2>, ...

SELECT conbio_id, conbio_price1
FROM dbo.conbio
ORDER BY conbio_price1;    --升序排列

--排序鍵：ORDER BY 子句中書寫的列名。
--【書寫順序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY

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

--2.升序（ASC）和降序（DESC）：

SELECT conbio_id, conbio_price1
FROM dbo.conbio
ORDER BY conbio_price1 DESC;    --降序排列

--ORDER BY conbio_id asc;    --降序排列
--【備忘】ORDER BY 子句中排列順序時會預設使用升序（ASC）進行排列。

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

--3.指定多個排序鍵

SELECT conbio_id, conbio_name, conbio_price1, conbio_price2
FROM dbo.conbio
ORDER BY conbio_price1, conbio_price2;

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

--4.NULL 值的順序：排序鍵中包含 NULL 時，會在開頭或末尾進行匯總。

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

--5.在排序鍵中使用 SELECT 子句中的別名

SELECT conbio_id AS id, conbio_name, conbio_price1 AS ht
FROM dbo.conbio
ORDER BY ht, id;

--【執行順序】FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

--【備忘】ORDER BY 子句可以使用 SELECT 子句中定義的別名，GROUP BY 子句不能使用別名。

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

--6.ORDER BY 子句中使用彙總函式

SELECT conbio_varieties, COUNT(*)
FROM dbo.conbio
GROUP BY conbio_varieties
ORDER BY COUNT(*);

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

--7.不建議使用列的編號進行排序，雖然可以

SELECT conbio_id ,
       conbio_name ,
       conbio_varieties ,
       conbio_price1 ,
       conbio_price2 ,
       conbio_date
FROM dbo.conbio
ORDER BY conbio_price1 DESC, conbio_id;

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

SELECT conbio_id ,
       conbio_name ,
       conbio_varieties ,
       conbio_price1 ,
       conbio_price2 ,
       conbio_date
FROM dbo.conbio
ORDER BY 4 DESC, 1;                --這裡使用列的編號，由於閱讀不便，不推薦使用

--【備忘】在 ORDER BY 子句中不要使用列的編號。

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

--歡迎關注個人公眾號：Zkcops

--2018/04/16　
　
由：zkcops 撰寫（希望能對你有所協助，轉載註明出處！）
--------------------------------------

【SQL.基礎構建-第三節(3/4)】