作 者：楊先民
審 稿：張智凱
文章編號：
出刊日期：
前言
前期我們大概介紹了借書系統的 T-SQL程式，也順道帶到了在
第一期所提到的生產線流程，所以我們在每一期開始，都要再複
習一下這個生產線流程，也就是如下圖所示：
圖1：T-SQL指令撰寫的流程。
而上一期的借書系統，有來源與想看的結果，只是看中間如何拚湊出轉換的指令。
那我們來看看這次的情境吧！
出勤系統
這個問題是有個學員利用電子郵件問我的問題，說他已經想了兩個星期都想不出該如何把句子寫出來。最後我大概花了10分鐘左右的時間，完成解答。並和他說要把這個例子當成範例給大家練習參考。
這個問題是一個出勤系統，並附上一個 Excel表格，經由轉換後，成為下列的SQL語法：
USE tempdb
GO
/****** Object: Table [dbo].[刷卡記錄] Script Date: 07/27/2011 12:29:23 ******/
GO
CREATE TABLE [dbo].[刷卡記錄](
[門別] [nvarchar](255) NULL,
[狀態] [nvarchar](255) NULL,
[姓名] [nvarchar](255) NULL,
[部門] [nvarchar](255) NULL,
[員工編號] [float] NULL,
[日期] [date] NULL,
[時間] [time](7) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[刷卡記錄] ([門別], [狀態], [姓名], [部門], [員工編號], [日期], [時間]) VALUES (N' 自強2F門禁考勤(進) ', N' 卡號正確 ', N'黃惠敏', N' 英濟 ', 9720035, CAST(0xBE330B00 AS Date), CAST(0x07003CCEEF640000 AS Time))
INSERT [dbo].[刷卡記錄] ([門別], [狀態], [姓名], [部門], [員工編號], [日期], [時間]) VALUES (N' 自強3F門禁考勤(進) ', N' 卡號正確 ', N'黃惠敏', N' 英濟 ', 9720035, CAST(0xBE330B00 AS Date),， CAST(0x078053B7FC950000 AS Time))
範例程式碼1：出勤系統的資料
範例程式碼1是由系統產生的出勤資訊，每個員工在進出大門時，都會刷卡，而這些刷卡記錄就會被記錄下來，出勤的資料會看起來會如圖2：
圖2：出勤的明細資料
而他希望這個明細資料，在經由SQL的處理之後，會變成如下方的結果：
--
--列出每天員工上下班時間 (每天最早刷卡以及最晚刷卡資訊)
--結果會呈現類似以下:
-- 日期 姓名 時間 狀態
--2011-01-07 陳奕騰 10:29:46.0000000 上班
--2011-01-07 陳奕騰 17:23:00.0000000 下班
--2011-01-07 陳宥蓁 08:01:52.0000000 上班
--2011-01-07 陳宥蓁 08:49:59.0000000 下班
--2011-01-07 陳逸弘 07:49:56.0000000 上班
這個範例中，我們已經有資料來源（也就是圖2），也有想要看到的結果，要如何利用已知的SQL指令來達成需求呢？
首先，必需了解，原始資料的刷卡記錄是會重複的，如圖2的黃惠敏在同一天，就列出了重複的資料，因為不管是出門還是進門都要刷卡，就會產生這麼多的明細資料。但我們希望只列出黃姓員工「每天」上班與下班時的資料即可，並不需要全部的資料。
所以，資料處理的順序在於：1.去除重複的資料，2.取出上下班資訊
去除重複資料，可能大家心裡會想到用distinct或是group by來做。但要取出上下班資訊，就覺得不是那麼容易（雖然上班時間是該日期的最早刷卡時間，下班時間是該日期的最晚刷卡時間，但因為並沒有一個 SQL指令專門取出上班時間或下班時間的資料）。
如果此題的重點還是擺在如何利用group by或distinct去除重複資料，那麼，我想我也會和該學員一樣連想兩個星期，也想不出個所以然。
為何？因為group by要使用彙總函數，但以本例而言，是要使用count還是sum呢？似乎都沒有。但其實問題在於我們腦中想到的SQL指令不夠多，無法把整個 SQL指令全部納入思考，才會發生這種「想到去除重複列」，只有想到group by或distinct可以用的情況。
但其實只要先抓到上、下班時間，再解決重複列的問題，自然答案就出來了！
而如何取出上、下班時間呢？只要了解上班是該天刷卡的第一筆記錄，而下班是該天刷卡的最後一筆記錄，腦中就應該會浮現rank()這類的函數了吧？
是的，最後就是用rank()這個函數來解決，先抓上班資訊，再抓下班資訊，然後利用union關鍵字把他們合在一起。至於去除重複列，只要利用rank()函數中的partition by，就可以達到和group by相同的效果，所以撰寫出來的程式如範例程式碼2：
select 日期,姓名,時間,status as 狀態 from (
SELECT *,N'上班' as status FROM (
SELECT 日期,姓名,時間,rank() over (partition by 日期,，姓名 order by 時間) as ranks FROM dbo.刷卡記錄) AS T
WHERE T.RANKS =1
UNION
SELECT *,N'下班' as status FROM (
SELECT 日期,姓名,時間,rank() over (partition by 日期,，姓名 order by 時間 DESC) as ranks FROM dbo.刷卡記錄) AS T
WHERE T.RANKS =1) as b
order by 日期,姓名
範例程式碼2：出勤系統的答案
最後得到的結果如圖3：
圖3：計算上、下班出勤記錄的結果
當然，結果如上圖會呈現上班與下班的狀態，是另外再使用了次查詢所做的設定。
這樣便很順利地解決了一個困難的問題，不過，也讓我們了解到，撰寫SQL指令，就某些層面上來説，很像學習英文，背的單字要多且廣，到需要時，能順利運用出來的機率就會大增。如果在前面幾個章節中，你對於每個個別的SQL指令都很熟悉，也親自測試如何使用。則到最後綜合演練時，就能把之前所學習的SQL指令從腦海中取出，並按照解題順序將結果產生出來。
總結
希望本期能夠幫助你，在思考自已公司情境時，能順利找出所謂的「解題順序」，再配合已會的SQL指令，將答案完成。不過必需提醒各位，本例的刷卡資料是在同棟大樓的員工，也沒有在不同大樓流動的情況，如果情境稍微修改一下，人員在不同棟大樓，不同部門的裝卡資訊都會被記錄下來，請列出每天每個人的上下班資訊，這個問題的答案可能就會不同（其實也還好，只是以天為分組條件罷了）
訂閱