sqlserver 奇妙的自聯系關系應用。本站提示廣大學習愛好者：（sqlserver 奇妙的自聯系關系應用）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver 奇妙的自聯系關系應用正文
第一列依照goodsid部分分組，然後在分組後的記載中依照audittime升序排序獲得序號，從而顯示某商品得第幾回變遷。
第二列是取該商品的最初變遷價錢newPrice，然後將該值賦到這個商品的其他行中，例如關於goodsid為1的，最初一個newprice為20，那末關於一切goodsid為1的記載curprice都寫為20，從而到達裡面控件散布的後果。
以下，比擬正常的數據記載：
須要增長特別的兩列後果為：
之前的做法是在C#辦事端將正常記載掏出來（先依照GoodsId和audittime排序再取的），然後遍歷全部數據集，新增了兩個列，經由過程第一次輪回處理expandfield的值成績，並用鍵值對奇妙的記載最初一次的newPrice，第二次輪回則將鍵值對記載的newPrice賦給統一個goodsid的curprice。代碼以下：
private void ChangeDataTable(DataTable dt)
{
dt.Columns.Add("curprice");
dt.Columns.Add("expandfield");
int goodsid = 0;
int index = 1; //指針
decimal curprice = 0;
IHashObject curpriceobj = new HashObject(); //鍵值對
//增長散布列
foreach(DataRow row in dt.Rows){
if (goodsid != Convert.ToInt32(row["goodsid"])) {
curpriceobj.Add("goodsid_"+goodsid, curprice);
//處置
index = 0;
goodsid = Convert.ToInt32(row["goodsid"]);
}
curprice = Convert.ToDecimal(row["newprice"]);
index += 1;
row["expandfield"] = "第" + index + "次變遷";
}
if(dt.Rows.Count != 0){ //添加最初一個商品的現行價
curpriceobj.Add("goodsid_"+goodsid, curprice);
}
//增長現行價
foreach (DataRow row in dt.Rows) {
row["curprice"] = curpriceobj["goodsid_" + row["goodsid"]];
}
}
但如今報表存儲進程必需分頁，分頁後還要支撐排序，假如客戶端請求依照最新價錢newPrice排序，很顯著如許經由過程辦事端轉換的數據源是沒方法支撐的（由於在數據庫分頁前的數據源並沒有newPrice這個字段）。
別的在C#辦事端處置，實際上是比擬占內存的，其一對DataTable遍歷了兩次，別的還用了暫時鍵值對對象，重復賦值是有機能消費。
綜上所述，終究我選擇放在數據庫來構建這兩列。
關於第一列部分排序，我很快想到了一個之前看過的語法 ROW_NUMBER() OVER(PARTITION BY...ORDER BY)，因而第一列expandfield很輕易就構建了，SQL以下：
SELECT '第' + CAST(ROW_NUMBER() OVER(PARTITION BY GoodsId ORDER BY audittime DESC) AS VARCHAR(10)) + '次變遷' AS expandfield,
GoodsId, price , discount , newPrice , begindate as [date]
FROM #test
但真正費事的是第二列，我摳破腦袋來想都沒想出來，乃至想到用暫時表來記載這個成果集，然後用游標來遍歷成果集，update這個表來修正記載，但照樣感到相當的繁瑣，並且機能很差。後來我就教了下公司的DBA，她給出了一種思緒，這類計劃不管從效力照樣可行性都是最優的。先貼出代碼以下：
WITH _TEMP AS (
SELECT ROW_NUMBER() OVER ( PARTITION BY GoodsId ORDER BY audittime DESC ) AS num2 ,
ROW_NUMBER() OVER ( PARTITION BY GoodsId ORDER BY audittime ) AS num ,
GoodsId, price , discount , newPrice , begindate as [date]
FROM #test
)
SELECT '第' + CAST(a.num AS VARCHAR(10)) + '次變遷' AS expandfield ,
a.goodsid , a.price , a.discount , a.newprice , a.date , b.newprice AS curprice
FROM _TEMP a
INNER JOIN _TEMP b ON a.GoodsId = b.GoodsId AND b.num2 = 1
這裡用了一個CTE表達式的自聯系關系查詢，從機能上說是很有優勢的，絕對於暫時表削減了對tempdb的開支，別的代碼的可讀性也比嵌套查詢更強，num列是用來構建第幾回變遷列的，這裡很奇妙很多加了依照audittime降序列的num2列，鄙人面對時記載b中，經由過程指定b.num2 = 1獲得一切goodsid最初變價的記載，再經由過程一個INNER JOIN就獲得了我們想要的值curprice值。
別的彌補一下，像下面存在多個列上排序字段時，SQL會選擇後者停止重排，即先按goodsid分組，然後再各個組中按audittime升序分列，假如將num放在前，num2放在後，是得不到我們要的記載。
