彙總函數的撰寫之二

by adonis 24. 九月 2012 13:24

作    者:楊先民
審    稿:張智凱

前言
   
上期講到了自訂彙總函數的撰寫,各位應該可以看的出來,平常會需要撰寫彙總函數的機率其實不高,因為大多數就是內建的彙總函數可以搞定的,無需動用自行撰寫彙總函數。


然而,還是總有這麼一天會用到的啦(笑),因為有些程式,用T-SQL指令就是慢(例如拚字串),而如果要動用到SQL的 Cursor才能撰寫程式的話,效能會更為的慘。所以本期要使用彙總函數來解決這種拚字串的問題。

彙總函數對於字串的處理

如果你想得到如下圖的結果,該怎麼做會比較好呢?

image

看到沒,這裡的 SalesOrderDetailsID的呈現方式是把相同的 SalesOrderID的 SalesOrderDetailsID利用字串串連的方式加總起來的,也就是說,SalesOrderHeader與 SalesOrderDetails這兩個資料表是1對多的關係,而明細資料表的 OrderDetailsID顯示出來並非以多列的方式呈現,而是以單列方式,但是以字串相串連的方式呈現。
換句話說,一般的關聯式資料庫只會呈現如下圖的結果:

image

而在設計資料庫的時後,也不可能會設計一個欄位可以存放多值的「多值屬性」,因為這樣是違反第一正規化的(無法對資料進行修改),所以這個在以往沒有彙總函數的時後,是無法有效的撰寫程式的(可以寫,但必需要用 Cursor,一列列將資料取出,然後自行串起資料,速度會很慢)。
然而,如果你在 SQL Server 2005之後的環境,就可以簡單的利用彙總函數達成我們的需求。

串連字串的彙總函數


為此,我們特別動用彙總函數來達成我們的需求。
首先,開啟 Visual Studio(本例是用 Visual Studio 2010),而且也下載安裝好SSDT這個工具(如果不知道要安裝,請參考第一期的內容),選擇新的 SQL CLR專案:

image

接下來新增一個 SQL CLR的彙總,如下圖(請不要選錯)

image

這時會出現預設的四大 void,也就是先前提過的 Init、Accumulate、Merge以及Terminate。
由於我們要串連字串,必需使用 System.Text類別中的 StringBuilder,所以記得 System.Text要 using進來。


接下來,在初始的地方把 StringBuilder 類別 new起來:
    public void Init()
    {
        intermediateResult = new StringBuilder();
    }
Accumulate則是實際將字串串連起來的地方,先判斷 value這個值是否為 Null,如果不是,就利用 Append的 method將字串串連起來。


    public void Accumulate(SqlString value)
    {

        if (value.IsNull)
        {
            return;
        }
        intermediateResult.Append(value.Value).Append(',');

    }


Merge部分其實可寫可不寫,最後Terminate的部份記得寫就好了,因為要把字串回傳回來:

    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (intermediateResult != null && intermediateResult.Length > 0)
            output = intermediateResult.ToString(0, intermediateResult.Length - 1);
        return new SqlString(output);
    }

最後,把這支程式發行,就可以來試看看我們的程式是否可以順利運行。
(新的名詞叫「發行」,不是叫佈署,還是提醒一下各位)

執行以下的 group by程式(彙總函數要在 group by底下運行,這個上期有提過):

select o.SalesOrderID,OrderDate,dbo.Concatenate(SalesOrderNumber) as SalesOrderDetailsID from [Sales].[SalesOrderHeader] AS o inner join Sales.SalesOrderDetail as od
on o.SalesOrderID = od.SalesOrderID
group by o.SalesOrderID,OrderDate

彙總函數是在 dbo.Concatenate(SalesOrderNumber)這裡,目的就是要將 SalesOrderNumber串起來,結果就會是如下圖:

image

這種程式,如果你不用 .NET來寫,用 T-SQL絕對會讓你寫非常久不說,而且還會讓效能不好,但用 .NET來說,處理字串本來就是它的強項,所以一點也不會有效能不好的問題喲!

Tags:

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List