這樣的 SQL如何寫 (四)

by adonis 22. 五月 2012 13:14

作    者:楊先民
審    稿:張智凱
文章編號:
出刊日期:   

前言
   
前期我們大概介紹了借書系統的 T-SQL程式,也順道帶到了在
第一期所提到的生產線流程,所以我們在每一期開始,都要再複
習一下這個生產線流程,也就是如下圖所示:
image

圖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:

image

圖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:

image

圖3:計算上、下班出勤記錄的結果
當然,結果如上圖會呈現上班與下班的狀態,是另外再使用了次查詢所做的設定。
這樣便很順利地解決了一個困難的問題,不過,也讓我們了解到,撰寫SQL指令,就某些層面上來説,很像學習英文,背的單字要多且廣,到需要時,能順利運用出來的機率就會大增。如果在前面幾個章節中,你對於每個個別的SQL指令都很熟悉,也親自測試如何使用。則到最後綜合演練時,就能把之前所學習的SQL指令從腦海中取出,並按照解題順序將結果產生出來。

總結

希望本期能夠幫助你,在思考自已公司情境時,能順利找出所謂的「解題順序」,再配合已會的SQL指令,將答案完成。不過必需提醒各位,本例的刷卡資料是在同棟大樓的員工,也沒有在不同大樓流動的情況,如果情境稍微修改一下,人員在不同棟大樓,不同部門的裝卡資訊都會被記錄下來,請列出每天每個人的上下班資訊,這個問題的答案可能就會不同(其實也還好,只是以天為分組條件罷了)

Tags:

評論 (1) -

fred
fred Taiwan
2012/7/20 下午 04:49:26 #

SELECT 日期,姓名,Min(時間) as 上班 ,Max(時間) as 下班  
FROM  刷卡記錄
Group by  日期 , 姓名
order by 日期,姓名

這樣可以嗎?

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List