這樣的 SQL如何寫 (四)

by adonis 27. 一月 2014 17:14

前言
前幾天在上課時,有位學員問了我一個問題,不過這個問題相當
的棘手,主要是因為資料庫設計以及資料呈現的問題,所以我
思考了半天決定把這篇文章寫出來,並且看看各位是否還有別的解決方法。

資料來源以及想看到的結果
問題是這樣子的,目前有一個資料表,長的如圖下一般:

image

當然,原來的問題並不是長這個樣子的,我是利用範例資料庫 Northwind稍微做了一些變化,但原則上是差不多如此的。
由圖可以看的出來, OrderID以及 ProductID兩個合起來是不重複的,但是使用者是希望能夠得到如下圖的結果:

image

有沒有感覺有什麼不同?可以和第一張圖比對一下,我們只看10248這筆訂單記錄,U與 Qty的欄位竟然是完全相同,而 OrderID與 ProductID兩個欄位合起來是 unique key。
怎麼會有這樣的使用者需求?
這個使用者需求就說來話長了,原來的需求並不是如訂單一樣的資料內容,而是某一家銀行業者的需求,該銀行有貸款,而借款人會有抵押品,抵押品會有抵押品編號以及抵押品的細項,細項也是有編號的,但是要列出借款人的抵押品資訊時,只需要顯示眾多抵押品的第一項即可。

老實說第一次聽到有這樣的需求,直覺的就是認為這樣的資料結構設計應該是有問題的,後來一問才知資料是從大型主機匯入到關聯式資料庫中,可能原先大型主機的資料結構關係,所以直接匯到關聯式資料庫就長的這個樣子,而使用者需求怪異的關係,即便要改,資料庫結構也無法改了,只好硬著頭皮看能不能用 SQL指令做出來。

一般來說,回顧一下之前所學到的「來源」→「SQL指令」→「想看的結果」,如下圖:

image

現在資料來源是有了,結果也有了,但是問題卡在以本例而言, SQL指令大概無法寫出來。
為什麼?因為原結果中的 OrderID、Productid與 U、Qty是兩個結果集合起來的,無論使用 group by或是什麼樣的去除重複句法,都很難單獨只抽取出 detail中的第一筆 U以及 Qty兩個欄位。

而且這個例子,即便使用子查詢內圈的查詢結果與外圈的查詢結果相同,即取回第一筆 Qty的明細,也不是非常容易撰寫。

這個時後,就需要使用我們的秘密武器了…

當知道這個結果集是兩個資料合併起來的內容,當無法有效的使用 Join或是 group by,或是 partition 時,還有另外一個方式,就是撰寫 table-value函數搭配 apply的句法。

是的,OrderID以及 ProductID就當成一個資料集,而 U與 Qty當成另一個資料集,透過輸入 OrderID這個參數來達成結果,要有效的合在一起查詢,則是利用 apply的句法。

所以就開始建立一個 table-value的函數如下:


create function fn_showone_details
(@OrderID int)
returns table
as
return
(select top 1 UnitPrice as U, Quantity as Qty from [Order Details]
where OrderID = @OrderID
)

這個函數很單純,就是輸入 OrderID,然後取出 U以及 Quantity這兩個結果集,而且只取出第一筆資料,這樣再搭配我們的原先資料集,利用 apply 合在一起,如下:

SELECT         OrderID, ProductID, U, Qty
FROM             [Order Details]
cross apply fn_showone_details(OrderID)

這樣就會得到我們想要的結果

image

怎麼樣,是不是很簡單呢?事實上這個問題我想過用很多種方式來做,第一時間想到的是次查詢,但試了半天發現不好寫,很容易自己搞錯語法,用 table value函數加上 apply,是我目前發現最好寫的,也很容易解決問題。

image

Tags:

評論 (1) -

Allen
Allen Taiwan
2014/9/8 上午 01:40:19 #

但是這樣來說 U以及 Quantity這兩個結果集就顯得沒有意義了,而inquire此兩無意義的欄位到Buffer Pool 只會占用記憶體,加上如果此2欄位沒有索引但OrderID 有索引,子查詢就會變成INDEX SCAN的方式來查詢,就會降低查詢的時間,會拖效能。

回覆

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List