mysql學習（二）-Group by與having理解

標籤：擷取   客戶   convert   維護   work   統計   class   提取   table   

注意：select 後的欄位，必須要麼包含在group by中，要麼包含在having 後的彙總函式裡。一. GROUP BY 是分組查詢, 一般 GROUP BY 是和彙總函式配合使用

group by 有一個原則,就是 select 後面的所有列中,沒有使用彙總函式的列,必須出現在 group by 後面（重要）

例如,有如下資料庫表：

A	B
1	ABC
1	BCD
1	ASDFG

 

 如果有如下查詢語句（該語句是錯誤的，原因見前面的原則）

1 select A,B from table group by A  

該查詢語句的意圖是想得到如下結果(當然只是一相情願) 

 

A     B 
       abc 
1     bcd 

       asdfg 

右邊3條如何變成一條,所以需要用到彙總函式，如下(下面是正確的寫法):

1 select A,count(B) as 數量 from table group by A 

這樣的結果就是 

A	數量
1	3

 

二、 Having

where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾資料，條件中不能包含聚組函數，使用where條件顯示特定的行。

having 子句的作用是篩選滿足條件的組，即在分組之後過濾資料，條件中經常包含聚組函數，使用having 條件顯示特定的組，也可以使用多個分組標準進行分組。

having 子句被限制子已經在SELECT語句中定義的列和彙總運算式上。通常，你需要通過在HAVING子句中重複彙總函式運算式來引用彙總值，就如你在SELECT語句中做的那樣。例如：

1 SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2

三、使用compute和compute by

• 使用compute子句允許同時觀察查詢所得到各列的資料的細節以及統計各列資料所產生的匯總列
• 　　select * from work [查詢所得到的各列的資料的細節]
•  　  compute max(基本工資),min(基本工資) [統計之後的結果]
• 這個例子中沒有使用by關鍵字,返回的結果是最後添加了一行基本工資的最大值和最小值,也可增加by關鍵字.
• 例:   select * from work order by 學曆
•         compute max(基本工資),min(基本工資) by 學曆
• 比較:select 學曆,max(基本工資),min(基本工資) from work group by 學曆
• 說明:1:compute子句必須與order by子句用在一起
•         2:compute子句可以返回多種結果集.一種是體現資料細節的資料集,可以按分類要求進行正確的分類；另一種在分類的基礎上進行匯總產生結果.
•         3:而group by子句對每一類資料分類之後只能產生一個結果,不能知道細節

 

非相關查詢：

1:返回每個美國員工都為其處理過訂單的所有客戶

--思路：1:Employees表中擷取美國員工總數2:Orders表中查詢美國員工處理的Order,對CustomerID分組後,統計其不同的EmployeeID正好等於美國員工總數

Select    CustomerIDFrom    OrdersWhere    EmployeeID In -- 得到美國員工服務 的客戶 (        Select            EmployeeID        From            Employees        Where            Country = N‘USA‘    ) -- 得到全部美國員工idgroup by    CustomerID -- 按客戶分組Having    Count(Distinct EmployeeID) = -- 為其處理訂單的distinct 員工數等於美國總員工數 (        Select            Count(*)        From            Employees        Where            Country = N‘USA‘    ) -- 美國員工總數

2：

返回在每月最後實際訂單日期發生的訂單(每月最後訂單日期可能不是每月最後一天)

--思路：子查詢按月分組得到每月最近訂單日期

Select    OrderID,    CustomerID,    EmployeeID,    OrderDateFrom    OrdersWhere    OrderDate In (        Select            Max(OrderDate)        From            Orders        Group by            Convert (char(6), OrderDate, 112)    ) -- 112表示YYYYMMDD char (6) 提取YYYYMM

 3.

Select字句在邏輯上是SQL語句最後進行處理的最後一步，所以，以下查詢會發生錯誤：

SELECT    OrderYear,    COUNT(DISTINCT CustomerID) AS NumCustsFROM    (        SELECT            YEAR (OrderDate) AS OrderYear,            CustomerID        FROM            dbo.Orders    ) AS DGROUP BY    OrderYear

因為group by是在Select之前進行的，那個時候orderYear這個列並沒有形成。

如果要查詢成功，可以像下面進行修改：

SELECT    OrderYear,    COUNT(DISTINCT CustomerID) AS NumCustsFROM    (        SELECT            YEAR (OrderDate) AS OrderYear,            CustomerID        FROM            dbo.Orders    ) AS DGROUP BY    OrderYear

還有一種很特殊的寫法：

SELECT    OrderYear,    COUNT(DISTINCT CustomerID) AS NumCustsFROM    (        SELECT            YEAR (OrderDate),            CustomerID        FROM            dbo.Orders    ) AS D (OrderYear, CustomerID)GROUP BY    OrderYear

這種寫法的，因為更清晰，更明確，更便於維護。

在查詢中使用參數定向產生一批結果，這個技巧沒有什麼好說的。

巢狀查詢，在處理邏輯上是從裡向外進行執行的。

mysql學習（二）-Group by與having理解