主頁 > 知識庫 > sqlserver 巧妙的自關(guān)聯(lián)運用

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

熱門標簽:重慶防封電銷機器人供應商 溫州語音外呼系統(tǒng)代理 南京電銷外呼系統(tǒng)運營商 智能語音外呼系統(tǒng)哪個牌子好 山西語音外呼系統(tǒng)價格 400電話申請需要開戶費嗎 西安青牛防封電銷卡 北京辦理400電話多少 威海智能語音外呼系統(tǒng)
第一列按照goodsid局部分組,然后在分組后的記錄中按照audittime升序排序得到序號,從而顯示某商品得第幾次變遷。

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

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

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

以前的做法是在C#服務(wù)端將正常記錄取出來(先按照GoodsId和audittime排序再取的),然后遍歷整個數(shù)據(jù)集,新增了兩個列,通過第一次循環(huán)解決expandfield的值問題,并用鍵值對巧妙的記錄最后一次的newPrice,第二次循環(huán)則將鍵值對記錄的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){ //添加最后一個商品的現(xiàn)行價
curpriceobj.Add("goodsid_"+goodsid, curprice);
}
//增加現(xiàn)行價
foreach (DataRow row in dt.Rows) {
row["curprice"] = curpriceobj["goodsid_" + row["goodsid"]];
}
}

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

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

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

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

標簽:宜春 貸款群呼 黃山 河源 濟寧 金昌 新余 中衛(wèi)

巨人網(wǎng)絡(luò)通訊聲明:本文標題《sqlserver 巧妙的自關(guān)聯(lián)運用》,本文關(guān)鍵詞  sqlserver,巧妙,的,自,關(guān)聯(lián),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《sqlserver 巧妙的自關(guān)聯(lián)運用》相關(guān)的同類信息!
  • 本頁收集關(guān)于sqlserver 巧妙的自關(guān)聯(lián)運用的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章