第四堂：家事管理 - 從計算年紀理解 SELECT 的常客(日期函式、數學函式、型態轉換)

第 11 屆 iThome 鐵人賽

DAY 4

Software Development

從問題理解與活用SQL語法系列第 4 篇

第四堂：家事管理 - 從計算年紀理解 SELECT 的常客(日期函式、數學函式、型態轉換)

11th鐵人賽

通靈亡

2019-09-20 23:58:54

5215 瀏覽

分享至

一、回顧：第三堂情境練習題

題目：統計各部門的員工薪資總和

https://www.tutorialrepublic.com/codelab.php?topic=sql&file=select-all

依照各部門薪水總和最高到薪水總和最低，依序列出部門員工薪資總和大於10000的部門ID、部門薪資總和。沒有部門編號的員工(dept_id <> 'null') 不包含在資料的統計結果內

實作結果

SELECT dept_id,
       SUM(salary) AS "薪資總和"
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING SUM(salary) > 10000
ORDER BY "薪資總和"

複習內容：分類後的統計

統計前的分類→統計前後的過濾條件→挑選被統計與沒有被統計的欄位

理解順序1:統計前的分類基準:GROUP BY

FROM employees
GROUP BY dept_id

理解順序2:統計前的資料過濾 WHERE 和統計後的資料過濾HAVING

WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING SUM(salary) > 10000
ORDER BY "薪資總和"

理解順序3：挑選被統計的欄位(SQL Aggregate Functions)與沒有被統計的欄位

SELECT dept_id,
	   SUM(salary) AS "薪資總和"
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING SUM(salary) > 10000
ORDER BY "薪資總和"

衍伸補充：比較運算子(大於、等於、小於、IS NULL、IS NOT NULL)

在sql的數字比較跟大多數的C語言程式語言相同，不過有兩點需要注意：

(一) 相等與不相等
1.相等：= (一個等號)
例如，A欄位是否等於B欄位： A = B

2.不相等：<> (大於小於)
例如，A欄位是否不等於B欄位：A <> B

(二) IS NULL 和 IS NOT NULL
在資料庫系統當中，不是每個欄位都會有值
如果沒有值，在資料庫以「NULL」表示「空值」

1.判斷是空值：IS NULL
例如，A欄位是否為空值：A IS NULL

2.判斷是否有值： IS NOT NULL
例如，A 欄位是否有值：A IS NOT NULL

二、準備資料

DB Fiddle

網址:
https://www.db-fiddle.com/

建立表格與新增資料的DDL SQL

CREATE TABLE Family (
  `FamilyId` VARCHAR(100) PRIMARY KEY,
  `FamilyName` VARCHAR(5),
  `FamilySex` VARCHAR(2),
  `BirthDate` DATETIME,
  `PhoneNumber` VARCHAR(10)
) CHARACTER SET=utf8;

INSERT INTO Family
  (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`)
VALUES
  ('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11', 'NULL'),
  ('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10', '0934567890'),
  ('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13', 'NULL'),
  ('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10', '0944623456'),
  ('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23', '0977654258'),
  ('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03', '0944589456');

SQL Server 環境：

CREATE TABLE [dbo].[Family](
	[FamilyId] [nvarchar](100) NOT NULL,
	[FamilyName] [nvarchar](10) NULL,
	[FamilySex] [nvarchar](10) NULL,
	[BirthDate] [datetime] NULL,
	[PhoneNumber] [nvarchar](20) NULL,
 CONSTRAINT [PK_Family] PRIMARY KEY CLUSTERED 
(
	[FamilyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'泰肝', N'女生', CAST(0x000099C400000000 AS DateTime), NULL)
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'泰熱', N'男生', CAST(0x0000917100000000 AS DateTime), N'0934567890')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'泰胖', N'女生', CAST(0x0000937800000000 AS DateTime), NULL)
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'泰瘦', N'女生', CAST(0x00007A0400000000 AS DateTime), N'0944623456')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'泰冷', N'男生', CAST(0x000087A000000000 AS DateTime), N'0977654258')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'泰賢', N'男生', CAST(0x00006DFD00000000 AS DateTime), N'0944589456')

三、問題情境

列出每位家庭成員的「姓名」、「生日」，並根據成員的出生年月日計算並顯示各自的「年齡」。

MySQL 實作結果

SELECT Family.FamilyName AS "姓名",
       CAST(Family.BirthDate AS DATE) AS "生日 (DATE 預設格式)",
       CONCAT(YEAR(Family.BirthDate) , '年' , MONTH(Family.BirthDate) , '月' , DAY(Family.BirthDate), '日')  AS "生日(中文)", 
       FLOOR(DATEDIFF(now(), Family.BirthDate) / 365.25) AS "年齡"
FROM Family;

姓名	生日 (DATE 預設格式)	生日(中文)	年齡
泰肝	2007-10-11	2007年10月11日	11
泰熱	2001-12-10	2001年12月10日	17
泰胖	2003-05-13	2003年5月13日	16
泰瘦	1985-07-10	1985年7月10日	34
泰冷	1995-01-23	1995年1月23日	24
泰賢	1977-02-03	1977年2月3日	42

SQL Server 實作結果

SELECT Family.FamilyName AS "姓名",
       CAST(Family.BirthDate AS DATE) AS "生日 (DATE 預設格式)",
       CONVERT(VARCHAR(11), Family.BirthDate, 111 ) AS "生日 (yyyy/mm/dd)",
       CONCAT(YEAR(Family.BirthDate) , '年' , MONTH(Family.BirthDate) , '月' , DAY(Family.BirthDate), '日')  AS "生日(中文)", 
       FLOOR(DATEDIFF(DAY, Family.BirthDate, GETDATE()) / 365.25) AS "年齡"
FROM Family

年紀計算說明

首先,使用 DATEDIFF(now(), Family.BirthDate)用來計算「生日」和「當天日期」的相差天數

SELECT DATEDIFF(now(), Family.BirthDate) AS "年齡"
FROM Family;

年齡
4362
6493
5974
12490
9006
15569

接著,將天數除以365.25 粗估歲數(四年一閏年，所以多出0.25)

SELECT DATEDIFF(now(), Family.BirthDate) / 365.25 AS "年齡"
FROM Family;

年齡
11.948
17.7823
16.3614
34.2012
24.6626
42.6311

最後,使用FLOOR 將計算出來的小數點捨去掉(還沒過生日的人今年不能算)

Query #1

SELECT FLOOR(DATEDIFF(now(), Family.BirthDate) / 365.25) AS "年齡"
FROM Family;

年齡
11
17
16
34
24
42

四、細節講解

重點一：常用的日期函式類型

手冊速查

對應範例SQL：

(YEAR(Family.BirthDate) + '年' + MONTH(Family.BirthDate) + '月' + DAY(Family.BirthDate)) + '日' AS "生日(中文)", 
       FLOOR(DATEDIFF(DAY, Family.BirthDate, GETDATE()) / 365.25) AS "年齡"

類型1.取得當下日期時間

每個資料庫系統使用的函式都不太一樣

SQL Server：GETDATE()
MySQL：NOW()、SYSDATE()
Oracle DB： SYSDATE 「他不是函式」
PostgreSQL：NOW()

類型2.取得某個日期時間的「年」、「月」、「日」

DATE_PART()
EXTRACT() (相當於SQL Server的Year()、Month()、DAY())

類型3.計算兩個日期的相差時間

非Oracle DB：DATEDIFF()
Oracle DB：使用TO_DATE()轉換後相減,例如：

TO_DATE('2000-01-02', 'YYYY-MM-DD') -  TO_DATE('2000-01-01', 'YYYY-MM-DD')

重點二：常用的數值函式

對應範例SQL：

FLOOR(DATEDIFF(DAY, Family.BirthDate, GETDATE()) / 365.25) AS "年齡"

CEIL：無條件進位
FLOOR：無條件捨去
ROUND：四捨五入
ABS：取絕對值
MOD：餘數除法 (SQL Server 使用 A % B 當作 A 除以 B 的餘數)

重點三：常用的型態轉換函式

非Oracle DB：Cast()、CONVERT()
Oracle DB： TO_XXXX() (例如 TO_NUMBER()、TO_DATE())

對應範例SQL：

CAST(Family.BirthDate AS DATE) AS "生日 (DATE 預設格式)",
CONVERT(VARCHAR(11), Family.BirthDate, 111 ) AS "生日 (yyyy/mm/dd)",
(YEAR(Family.BirthDate) + '年' + MONTH(Family.BirthDate) + '月' + DAY(Family.BirthDate)) + '日' AS "生日(中文)"

五、觀念延伸閱讀：運算式與函式

MySQL 超新手入門（4）運算式與函式
http://www.codedata.com.tw/database/mysql-tutorial-4-expression-function/

六、相關情境練習

題目資料庫網址(W3Schoool SQL Server 版)：

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

題目資料庫網址(W3Schoool MySQL 版)

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

使用資料表：

員工資料表 employees

說明

列出所有員工的「完整姓名」、「出生年月日」，並計算每個人活到2222-11-22的年齡歲數(實歲)

結果

第三堂：家事管理 - 分類後統計資料(GROUP BY → HAVING→SELECT、Aggregrate Function)

第五堂：家事管理 - 每日家事分工一覽表 (SELECT 子查詢)

系列文

從問題理解與活用SQL語法共 30 篇

RSS系列文訂閱系列文

56 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1064 組

團體組數

40 組

累計文章數

22203 篇

完賽人數

602 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js

IT邦幫忙

從問題理解與活用SQL語法系列 第 4 篇

第四堂：家事管理 - 從計算年紀 理解 SELECT 的常客(日期函式、數學函式、型態轉換)

一、回顧：第三堂 情境練習題

題目：統計各部門的員工薪資總和

實作結果

複習內容：分類後的統計

理解順序1:統計前的分類基準:GROUP BY

理解順序2:統計前的資料過濾 WHERE 和 統計後的資料過濾HAVING

理解順序3：挑選被統計的欄位(SQL Aggregate Functions)與沒有被統計的欄位

衍伸補充：比較運算子(大於、等於、小於、IS NULL、IS NOT NULL)

二、準備資料

DB Fiddle

SQL Server 環境：

三、問題情境

MySQL 實作結果

SQL Server 實作結果

年紀計算說明

四、細節講解

重點一：常用的日期函式類型

手冊速查

類型1.取得當下日期時間

類型2.取得某個日期時間的「年」、「月」、「日」

類型3.計算兩個日期的相差時間

重點二：常用的數值函式

重點三：常用的型態轉換函式

五、觀念延伸閱讀：運算式與函式

六、相關情境練習

題目資料庫網址(W3Schoool SQL Server 版)：

題目資料庫網址(W3Schoool MySQL 版)

使用資料表：

說明

結果

尚未有邦友留言

標記使用者

從問題理解與活用SQL語法系列第 4 篇

第四堂：家事管理 - 從計算年紀理解 SELECT 的常客(日期函式、數學函式、型態轉換)

一、回顧：第三堂情境練習題

理解順序2:統計前的資料過濾 WHERE 和統計後的資料過濾HAVING