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放在後，是得不到我們要的記錄。