sqlserver 巧妙的自關(guān)聯(lián)運(yùn)用
發(fā)布日期：2022-01-15 13:42 | 文章來(lái)源：源碼之家
第二列是取該商品的最后變遷價(jià)格newPrice，然后將該值賦到這個(gè)商品的其他行中，例如對(duì)于goodsid為1的，最后一個(gè)newprice為20，那么對(duì)于所有g(shù)oodsid為1的記錄curprice都寫為20，從而達(dá)到外面控件分布的效果。
如下，比較正常的數(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(); //鍵值對(duì)
//增加分布列
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)表存儲(chǔ)過(guò)程必須分頁(yè)，分頁(yè)后還要支持排序，如果客戶端要求按照最新價(jià)格newPrice排序，很明顯這樣通過(guò)服務(wù)端轉(zhuǎn)換的數(shù)據(jù)源是沒(méi)辦法支持的（因?yàn)樵跀?shù)據(jù)庫(kù)分頁(yè)前的數(shù)據(jù)源并沒(méi)有newPrice這個(gè)字段）。
另外在C#服務(wù)端處理，其實(shí)是比較占內(nèi)存的，其一對(duì)DataTable遍歷了兩次，另外還用了臨時(shí)鍵值對(duì)對(duì)象，反復(fù)賦值是有性能消耗。
綜上所述，最終我選擇放在數(shù)據(jù)庫(kù)來(lái)構(gòu)建這兩列。
關(guān)于第一列局部排序，我很快想到了一個(gè)以前看過(guò)的語(yǔ)法 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
但真正麻煩的是第二列，我摳破腦殼來(lái)想都沒(méi)想出來(lái)，甚至想到用臨時(shí)表來(lái)記錄這個(gè)結(jié)果集，然后用游標(biāo)來(lái)遍歷結(jié)果集，update這個(gè)表來(lái)修改記錄，但還是感覺(jué)相當(dāng)?shù)姆爆?，而且性能很差。后?lái)我請(qǐng)教了下公司的DBA，她給出了一種思路，這種方案無(wú)論從效率還是可行性都是最優(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
這里用了一個(gè)CTE表達(dá)式的自關(guān)聯(lián)查詢，從性能上說(shuō)是很有優(yōu)勢(shì)的，相對(duì)于臨時(shí)表減少了對(duì)tempdb的開(kāi)銷，另外代碼的可讀性也比嵌套查詢更強(qiáng)，num列是用來(lái)構(gòu)建第幾次變遷列的，這里很巧妙得多加了按照audittime降序列的num2列，在下面臨時(shí)記錄b中，通過(guò)指定b.num2 = 1取得所有g(shù)oodsid最后變價(jià)的記錄，再通過(guò)一個(gè)INNER JOIN就得到了我們想要的值curprice值。 另外補(bǔ)充一下，像上面存在多個(gè)列上排序字段時(shí)，SQL會(huì)選擇后者進(jìn)行重排，即先按goodsid分組，然后再各個(gè)組中按audittime升序排列，如果將num放在前，num2放在后，是得不到我們要的記錄。
復(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(); //鍵值對(duì)
//增加分布列
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)表存儲(chǔ)過(guò)程必須分頁(yè)，分頁(yè)后還要支持排序，如果客戶端要求按照最新價(jià)格newPrice排序，很明顯這樣通過(guò)服務(wù)端轉(zhuǎn)換的數(shù)據(jù)源是沒(méi)辦法支持的（因?yàn)樵跀?shù)據(jù)庫(kù)分頁(yè)前的數(shù)據(jù)源并沒(méi)有newPrice這個(gè)字段）。
另外在C#服務(wù)端處理，其實(shí)是比較占內(nèi)存的，其一對(duì)DataTable遍歷了兩次，另外還用了臨時(shí)鍵值對(duì)對(duì)象，反復(fù)賦值是有性能消耗。
綜上所述，最終我選擇放在數(shù)據(jù)庫(kù)來(lái)構(gòu)建這兩列。
關(guān)于第一列局部排序，我很快想到了一個(gè)以前看過(guò)的語(yǔ)法 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
但真正麻煩的是第二列，我摳破腦殼來(lái)想都沒(méi)想出來(lái)，甚至想到用臨時(shí)表來(lái)記錄這個(gè)結(jié)果集，然后用游標(biāo)來(lái)遍歷結(jié)果集，update這個(gè)表來(lái)修改記錄，但還是感覺(jué)相當(dāng)?shù)姆爆?，而且性能很差。后?lái)我請(qǐng)教了下公司的DBA，她給出了一種思路，這種方案無(wú)論從效率還是可行性都是最優(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)查詢，從性能上說(shuō)是很有優(yōu)勢(shì)的，相對(duì)于臨時(shí)表減少了對(duì)tempdb的開(kāi)銷，另外代碼的可讀性也比嵌套查詢更強(qiáng)，num列是用來(lái)構(gòu)建第幾次變遷列的，這里很巧妙得多加了按照audittime降序列的num2列，在下面臨時(shí)記錄b中，通過(guò)指定b.num2 = 1取得所有g(shù)oodsid最后變價(jià)的記錄，再通過(guò)一個(gè)INNER JOIN就得到了我們想要的值curprice值。 另外補(bǔ)充一下，像上面存在多個(gè)列上排序字段時(shí)，SQL會(huì)選擇后者進(jìn)行重排，即先按goodsid分組，然后再各個(gè)組中按audittime升序排列，如果將num放在前，num2放在后，是得不到我們要的記錄。
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信