第一列按照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放在后,是得不到我们要的记录。