快捷導(dǎo)航

sqlserver 巧妙的自關(guān)聯(lián)運(yùn)用

更新時(shí)間：2012年07月17日 21:36:21 作者：

最近在改報(bào)表分頁，遇到一個(gè)很棘手的問題，需要將比較正常的數(shù)據(jù)記錄新增加兩列

第一列按照goodsid局部分組，然后在分組后的記錄中按照audittime升序排序得到序號，從而顯示某商品得第幾次變遷。

第二列是取該商品的最后變遷價(jià)格newPrice，然后將該值賦到這個(gè)商品的其他行中，例如對于goodsid為1的，最后一個(gè)newprice為20，那么對于所有g(shù)oodsid為1的記錄curprice都寫為20，從而達(dá)到外面控件分布的效果。

如下，比較正常的數(shù)據(jù)記錄：

需要增加特殊的兩列效果為：

以前的做法是在C#服務(wù)端將正常記錄取出來（先按照GoodsId和audittime排序再取的），然后遍歷整個(gè)數(shù)據(jù)集，新增了兩個(gè)列，通過第一次循環(huán)解決expandfield的值問題，并用鍵值對巧妙的記錄最后一次的newPrice，第二次循環(huán)則將鍵值對記錄的newPrice賦給同一個(gè)goodsid的curprice。代碼如下：

復(fù)制代碼代碼如下:

 
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){ //添加最后一個(gè)商品的現(xiàn)行價(jià) 
curpriceobj.Add("goodsid_"+goodsid, curprice); 
} 
//增加現(xiàn)行價(jià) 
foreach (DataRow row in dt.Rows) { 
row["curprice"] = curpriceobj["goodsid_" + row["goodsid"]]; 
} 
} 

但現(xiàn)在報(bào)表存儲過程必須分頁，分頁后還要支持排序，如果客戶端要求按照最新價(jià)格newPrice排序，很明顯這樣通過服務(wù)端轉(zhuǎn)換的數(shù)據(jù)源是沒辦法支持的（因?yàn)樵跀?shù)據(jù)庫分頁前的數(shù)據(jù)源并沒有newPrice這個(gè)字段）。
另外在C#服務(wù)端處理，其實(shí)是比較占內(nèi)存的，其一對DataTable遍歷了兩次，另外還用了臨時(shí)鍵值對對象，反復(fù)賦值是有性能消耗。
綜上所述，最終我選擇放在數(shù)據(jù)庫來構(gòu)建這兩列。
關(guān)于第一列局部排序，我很快想到了一個(gè)以前看過的語法 ROW_NUMBER() OVER(PARTITION BY...ORDER BY)，于是第一列expandfield很容易就構(gòu)建了，SQL如下：

復(fù)制代碼代碼如下:

 
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 

但真正麻煩的是第二列，我摳破腦殼來想都沒想出來，甚至想到用臨時(shí)表來記錄這個(gè)結(jié)果集，然后用游標(biāo)來遍歷結(jié)果集，update這個(gè)表來修改記錄，但還是感覺相當(dāng)?shù)姆爆崳倚阅芎懿?。后來我請教了下公司的DBA，她給出了一種思路，這種方案無論從效率還是可行性都是最優(yōu)的。先貼出代碼如下：

復(fù)制代碼代碼如下:

 
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 

這里用了一個(gè)CTE表達(dá)式的自關(guān)聯(lián)查詢，從性能上說是很有優(yōu)勢的，相對于臨時(shí)表減少了對tempdb的開銷，另外代碼的可讀性也比嵌套查詢更強(qiáng)，num列是用來構(gòu)建第幾次變遷列的，這里很巧妙得多加了按照audittime降序列的num2列，在下面臨時(shí)記錄b中，通過指定b.num2 = 1取得所有g(shù)oodsid最后變價(jià)的記錄，再通過一個(gè)INNER JOIN就得到了我們想要的值curprice值。

另外補(bǔ)充一下，像上面存在多個(gè)列上排序字段時(shí)，SQL會選擇后者進(jìn)行重排，即先按goodsid分組，然后再各個(gè)組中按audittime升序排列，如果將num放在前，num2放在后，是得不到我們要的記錄。