2010年7月26日 星期一

SQL_找出最新日期、最舊日期、最大值、最小值

最近執行一個需要比較多查詢功能的案子,看看SQL資料夾裡面也有好多txt檔,也來整理一下這些內容好了。

這是一個常常都會運用到的查詢方式,比如說想要最新的巡察資料、最新的價格、最大的座標範圍、最老的歷史紀錄、最小的道路寬度等等;總之,各個不同的領域會有不同的需求,但通常我們不會只需要那個最大或最小的值,而是需要利用這個值去做為篩選的條件,例如:與目前時間最接近某個商品的報價。

要找出"最新"、"最大"、最舊"或"最小",不外乎就是使用Min與Max函式,比如說要由Data資料表取得最大年份:
select max(DateYear) as MaxYear from Data
要取得號碼是25的最小報價是哪天:
select min(price) as MinMoney from Data where NO=25
諸如此類的方式就可以取得我們想找到的數值。

但是我們不會只要這個值,我們會希望利用最大年份去查詢某一個資料表,所以必須使用子查詢的概念,將所獲得的"最大值"或"最小值"再去進行一次查詢:
Select A欄,B欄,C欄
from Data,(select max(DateYear) as MaxYear from Data) as MaxYearTable
where Data.DateYear = MaxYearTable.MaxYear and 其他篩選條件

由上面可以看的出來,我們所做的是對我們原始的資料表與取得最大值的資料表進行查詢,然後在where條件設定原始資料表的年份要等於子查詢所得的年份;如此就可以很輕鬆的篩選出某一個資料表某欄最大(最小)時的全部資料。

舉個簡單的例子:
1.資料庫原始資料:是一個有五個設施的資料,1、2、3設施巡查了一次,第4個巡查三次,第5個則看了四次,如果我們希望列出最新的巡察結果該怎麼做勒?

2. 首先我們先看看是否可以正確的抓到日期最大值,使用Max函式,看起來有正確的抓到資料。

3. 直接將這一個子查詢丟到where式中,可以發現成功的找到最新一筆資料。但這時候應該會感覺很奇怪,因為我們沒有指定要找哪一個設施,所以在11月5日那天只看了第五個設施,所以只出現一筆資料,那我們要怎麼看其他設施的最新資料呢?

4. 很單純的想,直接在子查詢跟主查詢加入一個SaveShipID=4的查詢條件,來獲得第4個設施的最新資料。當然,這樣可以很直觀的查出某個設施的最新巡查資料,但這跟我們一開始想像的有些落差,絕大部分的應用應該都是要列出"所有"設施的最新資料,而非某一項或某一特定物品的最新資料。

5. 需要列出每一種設施的最新資料,立刻就會想到group by這一個指令,來將不同設施的最新時間都抓出來。

6. 有了最新資料列表,最後就可以利用表格連接的方式取得"各設施最新的巡檢資料"。

7. 貼出最後一個SQL語法,比較方便複製。

select PatrolSaveShip.SaveShipID,[State],PatrolName,PatrolDate,Mark
from PatrolSaveShip,
(select SaveShipID, max(PatrolDate) as maxtime
from PatrolSaveShip group by SaveShipID) as idview
WHERE (PatrolSaveShip.SaveShipID = idview.SaveShipID)
and (PatrolSaveShip.PatrolDate = idview.maxtime)

第1句:要選擇的欄位
第2句:要查詢的原始表格
第3、4句:子查詢創出與原始表格要連接的最新時間表格
第5、6句:串聯表格的查詢子句。

4 則留言:

清風徐徐 提到...

ola 先生,您好
我是初學者,您的這個例子對我幫助非常大,非常感謝您的分享...

ola的家 提到...

^_^

匿名 提到...

請教一下如果是考勤刷卡資料。欄位有員工,刷卡時間。如何抓取每位員工每日第一筆(上班)時間和最後一筆(下班)時間

夕陽 提到...

真是很棒的文章

張貼留言