4. Select 語法

Where 條件式

標準單價設定檔StandardPrice

1. 一般查詢

   MSSQL 語法

   SELECT * FROM StandardPrice

2. 加入條件式使用一元運算子: <、>…等。

   MSSQL 語法

   SELECT * FROM Standardprice WHERE Standardprice > 100 AND StandardPrice < 380

3. BETWEEN ... AND

   MSSQL 語法

   SELECT * FROM Standardprice WHERE StandardPrice BETWEEN 28 AND 80
   SELECT * FROM Standardprice WHERE StandardPrice >=28 AND StandardPrice <=80

   

   效率 BETWEEN ... AND 較好

商品基本資料Products

1. 一般查詢

   MSSQL 語法

   SELECT ProductNo , ProductName , ProductSpec FROM Products

   

2. 條件式: IN(, , ,)
   複選，用於不連續範圍資料查詢

   MSSQL 語法

   SELECT ProductNo , ProductName , ProductSpec FROM Products
   WHERE ProductNo IN ('D01', 'D02', 'D04')

   

3. 模糊搜尋: %、-

   MSSQL 語法

   SELECT ProductNo , ProductName , ProductSpec FROM Products
   WHERE ProductName LIKE '%豬排%'

   

   MSSQL 語法

   SELECT ProductNo , ProductName , ProductSpec FROM Products
   WHERE ProductName LIKE '%肉'

   

排序 Order by

1. 由小至大，預設。

   MSSQL 語法

   SELECT CustomerNo , ShortName , CompanyAddress FROM Customers
   WHERE Companyaddress LIKE '新竹市%' ORDER BY ShortName ASC

   

   中文字是依照筆劃大小排序。

2. 由大至小。

   MSSQL 語法

   SELECT CustomerNo , ShortName , CompanyAddress FROM Customers
   WHERE Companyaddress LIKE '%竹東%' ORDER BY CustomerNo DESC

   

   先找到住址是 竹東，然後在用 客戶編號 由大至小排序。
   預設排序依 ASC

群組 Group by

1. 群組主要用途在於統計。

   MSSQL 語法

   SELECT Places.PlaceNo , Places.PlaceName ,
   SUM(Products.SafetyQty ) AS qty,
   AVG(Products.SalePrice ) AS price
   FROM Places INNER JOIN Products
   ON Places.PlaceNo = Products.PlaceNo
   GROUP BY Places.PlaceNo , Places.PlaceName
   ORDER BY Places.PlaceNo , Places.PlaceName 

   【結果】
   

   【說明】

   • 存放地點(Place) 關連到 商品(Produuct))
   • 聚合函數SUM、AVG 可以配合 GROUP BY 取得統計資料
   • SELECT Place.PlaceNo , Place.PlaceName 選擇的 Place.PlaceNo、Place.PlaceName 必須在ORDER BY Place.PlaceNo , Place.Placellame的Place.PlaceNo、Place.Placellame 範圍內，否則會找不到資料。

2. 經群組過濾後的第二層過濾搭配 HAVING
   條件: 平均單價(AVG(Products.SalePrice )) > 150

   MSSQL 語法

   SELECT Places.PlaceNo , Places.PlaceName ,
   SUM(Products.SafetyQty ) AS qty,
   AVG(Products.SalePrice ) AS price
   FROM Places INNER JOIN Products
   ON Places.PlaceNo = Products.PlaceNo
   GROUP BY Places.PlaceNo , Places.PlaceName HAVING AVG(Products.SalePrice ) > 150
   ORDER BY Places.PlaceNo , Places.PlaceName 

   【結果】
   

子查詢(SUB QUERY)

前言

1. 通常可用於統計資訊，屬於欄位層級的統計，在欄位中用子查詢。
2. 在查詢(SELECT)子句中的欄位使用查詢(SELECT)查詢另一組資料的方式
3. 子查詢中的WHERE 條件式可設定與外部表格的關聯:
   子查詢表格.欄位=外部表格.欄位

---

案例一

Vendors (供應商主檔)

資料行名稱	資料類型扼要	可為 Null	描述
🔑 Id	int	否	供應商主檔
IsEnabled	bit	否	合格供應商
TypeNo	nvarchar(50)	是	分類代號
VendorNo	nvarchar(50)	是	供應商編號
ShortName	nvarchar(50)	是	供應商簡稱
VendorName	nvarchar(250)	是	供應商名稱
CompanyTelphone	nvarchar(50)	是	公司電話
CompanyFax	nvarchar(50)	是	公司傳真
CompanyAddress	nvarchar(250)	是	公司地址
Remark	nvarchar(250)	是	備註

VendorType (供應商類別)

資料行名稱	資料類型扼要	可為 Null	描述
🔑 Id	int	否	供應商分類主檔
TypeNo	nvarchar(50)	是	分類代號
TypeName	nvarchar(50)	是	分類名稱
Remark	nvarchar(250)	是	備註

【需求說明】
要知道有哪些供應商類別，總共有幾家、佔比多少。

了解客戶需求，主幹線是 供應商類別(VendorType)

第一步: 先找出這時左側的主幹線VendorType的資料:

MSSQL 語法

SELECT TypeNo, TypeName
FROM VendorType
ORDER BY TypeNo 

第二步: 接這做右側的統計，透過一個欄位來完成。

在現有欄位裏，做手腳，增加一個計算欄位，這個欄位的資料來自於一個表格:
SELECT TypeNo, TypeName, () => SELECT TypeNo, TypeName, (SELECT ..) 查詢裡面有另一個查詢， 稱為 「子查詢」。
要跟 供應商家數 有關，所以需要另一個表格 Vendors 供應商主檔，(SELECT FROM Vendors)

聚合函數可以算 筆數、平均、合計 …等，現在要算家數，所以用 筆數: (SELECT COUNT(*) FROM Vendors) 把供應商所有家數(即筆數)取出， 但條件須關聯: 裡面的供應商類型需與外面的供應商類型一樣: (SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo)。
最後為每一查詢欄位給一別名。

MSSQL 語法

SELECT TypeNo AS 類別代號, TypeName AS 類別名稱,  (SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS 家數
FROM VendorType
ORDER BY TypeNo

接著想知道家數的佔比!

(目前家數 / 總家數 ) * 100

目前家數= (SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo)
總家數= (SELECT COUNT(*) FROM Vendors)

在增加一個欄位 ()，

MSSQL 語法

(SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) / (SELECT COUNT(*) FROM Vendors) * 100 

因為 家數、總家數、 (SELECT COUNT(*) FROM Vendors) * 100
都是整數，無法求得結果為小數，所以須轉型態。

SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo 改成
CAST((SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS DECIMAL)

SELECT COUNT(*) FROM Vendors 改成
CAST((SELECT COUNT(*) FROM Vendors) AS DECIMAL)

完整語法

MSSQL 語法

SELECT 
  TypeNo AS 類別代號, 
  TypeName AS 類別名稱,
  (SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS 家數,
    (CAST((SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS DECIMAL)  / 
    CAST((SELECT COUNT(*) FROM Vendors) AS DECIMAL) * 100 ) AS  佔比 
FROM VendorType
ORDER BY TypeNo;

【結果】

最後再轉成整數: CAST( □ AS INT)

CAST((SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS DECIMAL)  /  CAST((SELECT COUNT(*) FROM Vendors) AS DECIMAL) * 100

CAST( CAST((SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS DECIMAL)  /  CAST((SELECT COUNT(*) FROM Vendors) AS DECIMAL) * 100 AS INT)

完整語法

MSSQL 語法

SELECT 
TypeNo AS 類別代號, 
TypeName AS 類別名稱,
(SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS 家數,
  CAST( CAST((SELECT COUNT(*) FROM Vendors WHERE TypeNo = VendorType.TypeNo) AS DECIMAL)  /  CAST((SELECT COUNT(*) FROM Vendors) AS DECIMAL) * 100 AS DECIMAL)  AS 佔比
FROM VendorType
ORDER BY TypeNo;

【結果】

使用者可以拿這些資料來畫圖。

1. 打開 Excel，點選結果左上角來全選資料。
   
2. 滑鼠右鍵，選擇隨標頭一同複製
   
3. 複製後，到 Excel， 選擇選擇性貼上 貼上
   
   
4. Excel 中，圈選資料範圍後，插入 > 立體圓形圖
   
   

---

案例二

Overtimes

資料行名稱	資料類型扼要	可為 Null	描述
🔑 Id	int	否	加班單表頭
SheetNo	nvarchar(50)	是	加班單號
SheetDate	date	是	加班日期
DeptName	nvarchar(50)	是	部門名稱
Amounts	int	是	總加班費
Hours	int	是	總時數
Remark	nvarchar(250)	是	備註

【需求說明】

• 統計資料統計2022年全年度加班資料
• 將各部門 加班費 加以合計
• 將各部門 加班時數 加以合計
• 計算出各部門 平均每小時加班費
• 結果依 加班費 合計金額 由大到小 排列

【說明】

1. 判斷主幹線是什麼。 部門資料 (哪一部門加班費多少，哪一部門加班費多少)
2. 這裡特別之處，它的主幹線資料表與要統計用的資料表是同一個
   • 部門名稱: 從資料表Overtimes 獲取。
   • 加班費、時數: 也從資料表Overtimes 獲取。
     這時候不是由兩個資料表關聯，而是一個資料表(自己關聯自己)，要做出子查詢的效果。

步驟一: 找出主幹線(主查詢)

從加班資料表Overtimes獲取部門名稱。

MSSQL 語法

SELECT DeptName
FROM Overtimes
ORDER BY DeptName

【結果】
出現重覆值!

【解決方法】

1. 方法一: 使用 DISTINCT

   MSSQL 語法

   SELECT DISTINCT DeptName
   FROM Overtimes
   ORDER BY DeptName

2. 方法二: 使用 GROUP 作分類

   MSSQL 語法

   SELECT DeptName
   FROM Overtimes
   GROUP BY DeptName
   ORDER BY DeptName

以 GROUP BY 解決重覆值問題後，主幹線就出來了(哪一個部門，加班費多少，哪一個部門，加班費多少)

接著須看條件:
2022年全年度加班資料 WHERE ...，因資料只給加班日期 SheetDate，透過函數YEAR() 取的年度資料。 WHERE YEAR(SheetDate)

MSSQL 語法

FROM Overtimes WHERE YEAR(SheetDate) = 2022

完成主幹線的部分。

步驟二: 統計資料(子查詢)

1. 統計 加班費 Amounts
() AS 加班費

   因為作子查詢時，資料表同一個會誤判，所以裡外資料表名稱，用別名方式來區別，但加了別名後取出的欄位就要帶上別名，免得找錯。

   別名A為外面的資料表, 主查詢用。
   別名B為裡面的資料表, 子查詢用。

   MSSQL 語法

   SELECT A.DeptName AS 部門名稱,
   () AS 加班費
   FROM Overtimes AS A 
   WHERE YEAR(SheetDate) = 2022
   GROUP BY A.DeptName
   ORDER BY A.DeptName

   () AS 加班費 => (SELECT SUM(B.Amounts) FROM Overtimes AS B) AS 加班費

   加入兩資料表關聯條件:
   (SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName) AS 加班費 AND YEAR(SheetDate)=2022

   【完整SQL語法】

   MSSQL 語法

   SELECT A.DeptName AS 部門名稱,
   (SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName AND YEAR(SheetDate)=2022) AS 加班費
   FROM Overtimes AS A 
   WHERE YEAR(SheetDate) = 2022
   GROUP BY A.DeptName
   ORDER BY A.DeptName

   【結果】
   

2. 統計 **加班時數Hours **
   () AS 加班時數

   同理可得:

   MSSQL 語法

   (SELECT SUM(B.Hours) FROM Overtimes AS B WHERE B.DeptName = A.DeptName AND YEAR(SheetDate)=2022) AS 加班時數

   【完整SQL語法】

   MSSQL 語法

   SELECT A.DeptName AS 部門名稱,
   (SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName) AS 加班費,
   (SELECT SUM(B.Hours) FROM Overtimes AS B WHERE B.DeptName = A.DeptName) AS 加班時數
   FROM Overtimes AS A 
   WHERE YEAR(SheetDate) = 2022
   GROUP BY A.DeptName
   ORDER BY A.DeptName

   【結果】
   

步驟三:

結果要依 加班費 合計金額 由大到小 排列

MSSQL 語法

ORDER BY 加班費 DESC

最後結果，用同樣手法在 Excel 製表。

案例三

Overtimes

資料行名稱	資料類型扼要	可為 Null	描述
🔑 Id	int	否	加班單表頭
SheetNo	nvarchar(50)	是	加班單號
SheetDate	date	是	加班日期
DeptName	nvarchar(50)	是	部門名稱
Amounts	int	是	總加班費
Hours	int	是	總時數
Remark	nvarchar(250)	是	備註

【需求說明】

• 統計資料统計 2020年-2022 年 只計算 上半年 年加班資料
• 將各部門 加班費 加以合計
• 結果依 部門名稱 由小到大排列

【說明】

1. 判斷主幹線是什麼。 部門資料 (哪一部門加班費多少，哪一部門加班費多少)
2. 這裡特別之處，它的主幹線資料表與要統計用的資料表是同一個
   • 部門名稱: 從資料表Overtimes 獲取。
   • 加班費、時數: 也從資料表Overtimes 獲取。
     這時候不是由兩個資料表關聯，而是一個資料表(自己關聯自己)，要做出子查詢的效果。

步驟一: 找出主幹線(主查詢)
從加班資料表Overtines獲取部門名稱。

MSSQL 語法

SELECT A.DeptName AS 部門名稱
FROM Overtimes AS A
WHERE YEAR(A.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6
GROUP BY A.DeptName
ORDER BY A.DeptName

【結果】

完成主幹線的部分。

步驟二: 統計資料(子查詢)

統計: 合計各部門的加班費 Amounts
() AS 加班費

因為作子查詢時，資料表同一個會誤判，所以裡外資料表名稱，用別名方式來區別，但加了別名後取出的欄位就要帶上別名，免得找錯。

別名A為外面的資料表, 主查詢用。
別名B為裡面的資料表, 子查詢用。

MSSQL 語法

SELECT A.DeptName AS 部門名稱,
() AS "2020年上半年加班費",
() AS "2021年上半年加班費",
() AS "2022年上半年加班費",
FROM Overtimes AS A
WHERE YEAR(A.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6
GROUP BY A.DeptName
ORDER BY A.DeptName  

() AS "2020年上半年加班費" => (SELECT SUM(B.Amounts) FROM Overtimes AS B) AS "2020年上半年加班費"

加入兩資料表關聯條件:

MSSQL 語法

(SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName) AS "2020年上半年加班費"
(SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName) AS "2021年上半年加班費"
(SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName) AS "2022年上半年加班費"

加入條件: 年度，月份 YEAR(A.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6

MSSQL 語法

(SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName AND YEAR(A.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6) AS "2020年上半年加班費"
(SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName AND YEAR(A.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6) AS "2021年上半年加班費"
(SELECT SUM(B.Amounts) FROM Overtimes AS B WHERE B.DeptName = A.DeptName AND YEAR(A.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6) AS "2022年上半年加班費"

【完整SQL語法】

MSSQL 語法

SELECT P.DeptName AS 部門名稱,
(SELECT SUM(A.Amounts) FROM Overtimes AS A WHERE A.DeptName = P.DeptName AND
YEAR(A.SheetDate) = 2020 AND MONTH(A.SheetDate) BETWEEN 1 AND 6) AS '2020年上半年加班費',
(SELECT SUM(A.Amounts) FROM Overtimes AS A WHERE A.DeptName = P.DeptName AND
YEAR(A.SheetDate) = 2021 AND MONTH(A.SheetDate) BETWEEN 1 AND 6) AS '2021年上半年加班費',
(SELECT SUM(A.Amounts) FROM Overtimes AS A WHERE A.DeptName = P.DeptName AND
YEAR(A.SheetDate) = 2022 AND MONTH(A.SheetDate) BETWEEN 1 AND 6) AS '2022年上半年加班費'
FROM Overtimes AS P
WHERE YEAR(P.SheetDate) BETWEEN 2020 AND 2022 AND MONTH(P.SheetDate) BETWEEN 1 AND 6
GROUP BY P.DeptName
ORDER BY P.DeptName

【結果】

最後結果，用同樣手法在 Excel 製表。