SQL SERVER With文法[轉]

標籤：des   style   blog   http   ar   io   color   os   使用   

今天在論壇上看到一個舉例，關於sql server 的樣本。1/25/50/100美分，多少種可能拼湊成2美元。

    看了其中第一條文法，放在SQL SERVER中測試，發現真的列舉出所有組合成2美元的方式。

   於是仔細研究文法，發現用了With關鍵字。

 

   發現很長時間沒有使用SQL SERVER資料庫，真的有點落後了。於是見到那學習了下 With關鍵字。

1.引言

 

現實項目中經常遇到需要處理遞迴父子關係的問題，如果把層次關係分開，放在多個表裡通過主外鍵關係聯結，最明顯的問題就是擴充起來不方便，對於這種情況，一般我們會建立一個使用自串連的表來存放資料。例如存放會員地區資料表結構可能是這樣：

 

列名	描述
location_id	地區編號
location_name	地區名稱
parentlocation_id	上級地區編號

 

或者某個部分的職員表結構可能如下所示：

 

列名	描述
employee_id	職員編號
employee_name	職員名稱
manager_id	職員的直接上級管理者,和employee_id進行自我聯結

 

通過類似表結構，我們就可以通過一個表理論上管理無限級數的父/子關係，但是當我們需要將這些資料讀取出來，不論是填充到一個樹中，或是使用級聯顯 示出來，需要花費一定的精力。傳統的做法，是做一個遞迴調用，首先串連資料庫將頂層資料（也就是parent_xxx為null的記錄）讀取出來，再對每 一條資料進行遞迴訪問填充集合，這種做法需要串連資料庫多次，顯然不是較好的解決方案，那麼我們能不能通過一次資料庫訪問，將資料全部讀取出來，並且為了 按照父子關係形成集合，使返回的資料滿足某種格式。

2. 分析

 

理想情況下，如果父/子關係資料時嚴格按照關係結構添加到資料庫中，亦即首先添加某條父記錄，接著添加該父記錄的子記錄，如果子記錄還包含子記錄的 話繼續添加，最終資料表中父/子關係按規則排列資料，我們就可以使用某種演算法填充集合，但是正如我們所說，這是理想情況，實際情況下資料經常會發生改變， 導致資料沒有規律可言，如所示，這樣的話讀取資料填充集合就不太容易的。

所以我們要做的就是通過查詢使資料庫返回的資料滿足這種格式，那麼我們的思路是首先尋找頂層（0層）記錄，再查詢第1層記錄，接下來是第2層、第3層直到第n層。因為層數是不確定的，所以仍然需要使用遞迴訪問。

SQL Server 2005中提供了新的with關鍵字，用於指定臨時命名的結果集，這些結果集稱為通用資料表運算式（CTE）。該運算式源自簡單查詢，並且在SELECT、 INSERT、UPDATE或DELETE 語句的執行範圍內定義。該子句也可用在 CREATE VIEW 語句中，作為該語句的 SELECT 定義語句的一部分。通用資料表運算式可以包括對自身的引用。這種運算式稱為遞迴通用資料表運算式。

其文法為：

 [ WITH <common_table_expression> [ ,...n ] ]<common_table_expression>::=expression_name[ ( column_name [ ,...n ] ) ]AS( CTE_query_definition )

使用with關鍵子的一個簡單樣本，以下代碼將tb_loc表中資料來源樣輸出：

WITH locs(id,name,parent)AS(    SELECT * FROM tb_loc)SELECT * FROM locs

為了建立良好層次記錄結構集，使用with關鍵字首先讀取頂層記錄，並且針對每一條頂層記錄讀取其子記錄，直到讀取到最底層級記錄，最後將所有的記錄組合起來，這裡用到了UNION ALL關鍵字，用於將多個查詢結果組合到一個結果集中。

接下來就可以使用該關鍵字建立預存程序返回結果集，並附加每條記錄所位於的“層”數，如所示：

最後需要在前台介面將其顯示出來，由於記錄已經按層次返回，需要做的就是按層次首其輸出，首先將第0層資料輸出，接下來將遍曆第0層資料，將第一層 資料添加到合適的父物件中，重複此過程直到填充結果。那麼這裡的難題就在於如何尋找父物件，我們當然可以遍曆集合，但是這麼做的話如果資料量很大將導致效 率低下。既然可以得到當前對象所位於的層的資訊，就也是這樹倒置的樹是一層一層向下填充的，我們可以定義一個臨時集合變數，儲存當前層上一層的所有父對 象，在插入當前層對象時遍曆集合變數以插入到合適的位置，同時我們還必須保證在逐層讀取資料時臨時集合變數中持有的始終時當前層上一層所有的對象，程式流 程圖如下所示：

根據以上分析，我們就可以編寫實現代碼了（為了方便，將本文中用到的資料表和建立記錄等SQL語句一併給出）。

3. 實現

3.1 開啟SQL Server 2005 Management Studio，選擇某個資料庫輸入以下語句建立表結構：

CREATE TABLE [tb_loc](    [id] [int],    [name] [varchar](16),    [parent] [int])GO

3.2 建立測試資料：

INSERT tb_loc(id,name,parent) VALUES( 1,‘河北省‘,NULL) INSERT tb_loc(id,name,parent) VALUES( 2,‘石家莊‘,1) INSERT tb_loc(id,name,parent) VALUES( 3,‘保定‘,1) INSERT tb_loc(id,name,parent) VALUES( 4,‘山西省‘,NULL) INSERT tb_loc(id,name,parent) VALUES( 5,‘太原‘,4) INSERT tb_loc(id,name,parent) VALUES( 6,‘新華區‘,2) INSERT tb_loc(id,name,parent) VALUES( 7,‘北焦村‘,6) INSERT tb_loc(id,name,parent) VALUES( 8,‘大郭村‘,6) INSERT tb_loc(id,name,parent) VALUES( 9,‘河南省‘,NULL) INSERT tb_loc(id,name,parent) VALUES( 10,‘大郭村南‘,8) INSERT tb_loc(id,name,parent) VALUES( 11,‘大郭村北‘,8) INSERT tb_loc(id,name,parent) VALUES( 12,‘北焦村東‘,7) INSERT tb_loc(id,name,parent) VALUES( 13,‘北焦村西‘,7) INSERT tb_loc(id,name,parent) VALUES( 14,‘橋東區‘,3) INSERT tb_loc(id,name,parent) VALUES( 15,‘橋西區‘,3) GO

3.3 建立pr_GetLocations預存程序：

CREATE PROCEDURE pr_GetLocationsASBEGIN    WITH locs(id,name,parent,loclevel)    AS    (        SELECT id,name,parent,0 AS loclevel FROM tb_loc        WHERE parent IS NULL        UNION ALL        SELECT l.id,l.name,l.parent,loclevel+1 FROM tb_loc l             INNER JOIN locs p ON l.parent=p.id    )    SELECT * FROM locsEND

3.4 在Visual Studio 2008裡建立解決方案並建立一個網站。

3.5 在網站中添加APP_Code目錄，並建立Location實體類，該類標識了所在地編號和名稱，並且儲存了父級所在地編號和它所包含的所有子所在地的集合：

public class Location{    public int Id    {        get;        set;    }    public string Name    {        get;        set;    }    public LocationCollection SubLocations    {        get;        set;    }    public int ParentId    {        get;        set;    }    public Location()    {        Id = 0;        Name = string.Empty;        SubLocations = new LocationCollection();        ParentId=0;    }}

3.5 以上代碼使用了LocationCollection集合類，使用泛型集合建立該類（同樣位於APP_Code目錄下）：

using System.Collections.Generic;public class LocationCollection:List<Location>{    }

3.6 在APP_Code目錄下建立DAO類用於訪問資料庫，添加必要的命名空間引用：

using System;using System.Data;using System.Data.SqlClient;public class DAO{}

3.7編寫GetLocations方法，返回所在地集合對象（請根據實際情況修改資料庫連接字串）：

public LocationCollection GetLocations(){    LocationCollection locs = new LocationCollection();    using (SqlConnection conn = new        SqlConnection("server=.;uid=sa;pwd=00000000;database=temp;"))    {        conn.Open();        SqlCommand cmd = new SqlCommand();        cmd.CommandText = "pr_GetLocations";        cmd.CommandType = CommandType.StoredProcedure;        cmd.Connection = conn;        SqlDataReader reader = cmd.ExecuteReader();                            int level = 0;        int oldlevel = 1;        LocationCollection container=new LocationCollection();        LocationCollection current = new LocationCollection();        while (reader.Read())        {            Location loc = GetLocationFromReader(reader, out level);            if (level == 0)            {                locs.Add(loc);                container.Add(loc);                                }            else            {                if (oldlevel != level)                {                    container.Clear();                    foreach (Location l in current)                        container.Add(l);                    current.Clear();                    oldlevel = level;                }                current.Add(loc);                CreateLocation(container, loc);            }                        }    }    return locs;}

在該方法按照以下步驟執行：

1. 使用命令對象對象執行pr_GetLocations預存程序返回結果集

2. 如果資料閱讀器讀取了資料（reader.Read方法返回true）執行：

2.1.從資料閱讀器目前記錄中讀取Location對象，並返回層數資訊（out level）

2.2.如果是第一層（level等於0）填充locs集合，並加入到container對象

2.3.如果不是第一層根據層標誌（oldlevel）判斷當前層是否是新的一層

2.4 如果當前層是新的一層清空container集合并將current集合中實體複製到container集合中，清空current集合并置層標誌（oldlevel）

2.5 將當前對象添加到current集合中

2.6 調用CreateLocation方法從container上層集合中匹配當前實體父級對象並加入父物件的子集合中

3. 重複第2步直到讀取完全部資料

可以看到container集合始終儲存了當前層的上層所有的實體物件，並且為了在更換層數後能夠正確的更新container集合，使用current集合儲存當前層的實體物件。

3.8 編寫GetLocationFromReader方法，用於從資料閱讀器中返回Location實體物件，並將層數資訊使用out參數返回：

private Location GetLocationFromReader(SqlDataReader reader, out int level){    Location loc = new Location();    loc.Id = Convert.ToInt32(reader["id"]);    loc.Name = Convert.ToString(reader["name"]);    object o = reader["parent"];    if (o != DBNull.Value)        loc.ParentId = Convert.ToInt32(o);    level = Convert.ToInt32(reader["loclevel"]);    return loc;}

3.9 編寫CreateLocation方法，該方法遍曆實體集合找到與當前實體物件的父級編號匹配的實體，並將當前實體加入到父級實體的子集合中：

private void CreateLocation(LocationCollection container, Location loc){    foreach (Location location in container)    {        if (location.Id == loc.ParentId)        {            location.SubLocations.Add(loc);            break;        }    }}

3.10 向Default.aspx頁面上添加TreeView控制項：

<asp:TreeView ID="trvLocation" runat="server" Font-Size="12px"    ShowLines="True"></asp:TreeView>

3.11 在Default.aspx頁面後置代碼中編寫BindData資料繫結方法：

private void BindData(){    DAO dao = new DAO();    LocationCollection locs = dao.GetLocations();    TreeNodeCollection nodes = CreateTreeNodes(locs);    foreach (TreeNode node in nodes)    {        trvLocation.Nodes.Add(node);    }}

3.12 BindData方法調用了CreateTreeNode方法返回節點集合，該方法中遞迴調用自身以得到全部所在地節點：

private TreeNodeCollection CreateTreeNodes(LocationCollection locs){    TreeNodeCollection nodeColl = new TreeNodeCollection();    foreach (Location loc in locs)    {        TreeNode node = new TreeNode(loc.Name, loc.Id.ToString());                if (loc.SubLocations.Count > 0)        {            TreeNodeCollection subColl = CreateTreeNodes(loc.SubLocations);            foreach (TreeNode subNode in subColl)                node.ChildNodes.Add(subNode);        }        nodeColl.Add(node);    }    return nodeColl;}

3.13 最後在頁面載入事件裡執行資料繫結：

protected void Page_Load(object sender, EventArgs e){    if (!IsPostBack)    {        this.BindData();    }}

3.14 在瀏覽器中預覽結果：

4. 總結

原來在處理類似父子關係時總是找不到好的解決辦法，現在通過SQL Server 2005裡的新特性可以較為合理的解決該類問題，在這裡主要用到了with關鍵字實現遞迴訪問，並且在輸出資料時同樣使用了遞迴的方法。如果各位有更好的實現方式，請不不吝賜教。

本文範例程式碼下載：範例程式碼

 

 

感謝各位支援，在SQL Server Management Studio中抓取了查詢單表和以上演算法的執行計畫，供大家參考吧

1.select * from tb_loc

2. exec pr_GetLocations

SQL SERVER With文法[轉]