巧妙的自關聯(lián)運用
更新時間:2012年08月09日 22:25:26 作者:
最近在改報表分頁,遇到一個很棘手的問題,需要將比較正常的數(shù)據(jù)記錄新增加兩列
第一列按照goodsid局部分組,然后在分組后的記錄中按照audittime升序排序得到序號,從而顯示某商品得第幾次變遷。
第二列是取該商品的最后變遷價格newPrice,然后將該值賦到這個商品的其他行中,例如對于goodsid為1的,最后一個newprice為20,那么對于所有goodsid為1的記錄curprice都寫為20,從而達到外面控件分布的效果。
如下,比較正常的數(shù)據(jù)記錄:
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排序,很明顯這樣通過服務端轉換的數(shù)據(jù)源是沒辦法支持的(因為在數(shù)據(jù)庫分頁前的數(shù)據(jù)源并沒有newPrice這個字段)。
另外在C#服務端處理,其實是比較占內存的,其一對DataTable遍歷了兩次,另外還用了臨時鍵值對對象,反復賦值是有性能消耗。
綜上所述,最終我選擇放在數(shù)據(jù)庫來構建這兩列。
關于第一列局部排序,我很快想到了一個以前看過的語法 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這個表來修改記錄,但還是感覺相當?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表達式的自關聯(lián)查詢,從性能上說是很有優(yōu)勢的,相對于臨時表減少了對tempdb的開銷,另外代碼的可讀性也比嵌套查詢更強,num列是用來構建第幾次變遷列的,這里很巧妙得多加了按照audittime降序列的num2列,在下面臨時記錄b中,通過指定b.num2 = 1取得所有goodsid最后變價的記錄,再通過一個INNER JOIN就得到了我們想要的值curprice值。
另外補充一下,像上面存在多個列上排序字段時,SQL會選擇后者進行重排,即先按goodsid分組,然后再各個組中按audittime升序排列,如果將num放在前,num2放在后,是得不到我們要的記錄。
第二列是取該商品的最后變遷價格newPrice,然后將該值賦到這個商品的其他行中,例如對于goodsid為1的,最后一個newprice為20,那么對于所有goodsid為1的記錄curprice都寫為20,從而達到外面控件分布的效果。
如下,比較正常的數(shù)據(jù)記錄:
需要增加特殊的兩列效果為:
復制代碼 代碼如下:
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排序,很明顯這樣通過服務端轉換的數(shù)據(jù)源是沒辦法支持的(因為在數(shù)據(jù)庫分頁前的數(shù)據(jù)源并沒有newPrice這個字段)。
另外在C#服務端處理,其實是比較占內存的,其一對DataTable遍歷了兩次,另外還用了臨時鍵值對對象,反復賦值是有性能消耗。
綜上所述,最終我選擇放在數(shù)據(jù)庫來構建這兩列。
關于第一列局部排序,我很快想到了一個以前看過的語法 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這個表來修改記錄,但還是感覺相當?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表達式的自關聯(lián)查詢,從性能上說是很有優(yōu)勢的,相對于臨時表減少了對tempdb的開銷,另外代碼的可讀性也比嵌套查詢更強,num列是用來構建第幾次變遷列的,這里很巧妙得多加了按照audittime降序列的num2列,在下面臨時記錄b中,通過指定b.num2 = 1取得所有goodsid最后變價的記錄,再通過一個INNER JOIN就得到了我們想要的值curprice值。
另外補充一下,像上面存在多個列上排序字段時,SQL會選擇后者進行重排,即先按goodsid分組,然后再各個組中按audittime升序排列,如果將num放在前,num2放在后,是得不到我們要的記錄。
相關文章
SQLSERVER編譯與重編譯發(fā)生場景及重用的利弊介紹
本文將介紹編譯的含義;執(zhí)行計劃重用的利弊以及重編譯的發(fā)生場景等等,為您學習SQLSERVER編譯與重編譯打下很好的基礎,感興趣的朋友可以了解下2013-01-01SQL使用登錄名連接數(shù)據(jù)庫報錯(錯誤代碼18456問題)
18456是因密碼或用戶名錯誤而使身份驗證失敗,如果用戶名密碼正確,則該登錄名沒有連接數(shù)據(jù)庫引擎權限,本文就來介紹一下解決方法,具有一定的參考價值,感興趣的可以了解一下2023-10-10用SQL腳本讀取Excel中的sheet數(shù)量及名稱的方法代碼
這篇文章介紹了用SQL腳本讀取Excel中的sheet數(shù)量及名稱的方法代碼,有需要的朋友可以參考一下2013-09-09sqlserver復制數(shù)據(jù)庫的方法步驟(圖文)
這篇文章主要介紹了sqlserver復制數(shù)據(jù)庫的方法步驟(圖文),文中通過圖文示例介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2021-04-04SQLServer2019 數(shù)據(jù)庫環(huán)境搭建與使用的實現(xiàn)
這篇文章主要介紹了SQLServer2019 數(shù)據(jù)庫環(huán)境搭建與使用的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2021-04-04SQL?Server安裝后本地服務器連接失敗的解決方法(僅供參考)
訪問SQLServer數(shù)據(jù)庫失敗時,最常見的錯誤是"連接服務器失敗,SQLServer不存在或訪問被拒絕",或者"未能建立與(local)的鏈接,SQLServer不存在或訪問被拒絕",這篇文章主要給大家介紹了關于SQL?Server安裝后本地服務器連接失敗的解決方法,需要的朋友可以參考下2023-11-11MySQL 數(shù)據(jù)庫 source 命令詳解及實例
這篇文章主要介紹了MySQL 數(shù)據(jù)庫 source 命令詳解及實例的相關資料,需要的朋友可以參考下2017-06-06SQLServer 數(shù)據(jù)庫故障修復頂級技巧之一
SQL Server 2005 和 2008 有幾個關于高可用性的選項,如日志傳輸、副本和數(shù)據(jù)庫鏡像。2010-04-04Sql Server中Substring函數(shù)的用法實例解析
在sqlserver中substring函數(shù)是用來處理字符串的,常用于字符串截取了,下面我來給大家介紹下Sql Server中Substring函數(shù)的用法實例解析,需要的朋友參考下吧2016-12-12