sqlserver 巧妙的自关联运用
来源:asp之家 发布时间:2012-07-21 14:55:12
第一列按照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放在后,是得不到我们要的记录。
猜你喜欢
- 代码如下:<% Response.Buffer = True '一般情况下,当用户请求
- 如何制作一个搜索引擎链接程序?多收集几个网站的,然后我们引用它到自己的页面中。接下来,我们要创建页面用于搜索:<center>&
- 第一次写技术博客,有不尽如人意的地方,还请见谅和指正。为什么想整理这方面的类容,我觉得就像油画家要了解他的颜料和画布、雕塑家要了解他的石材一
- IE6绝对定位的bug及其解决办法。position:absolute定位在IE6下存在left和bottom的定位错误问题:<!–I
- 索引 经常要查询的语句,则给它建一个索引 表连接 select T_Oders as o join T_Customers as C on
- 尽管人们期望在屏幕上有些改变,但是CSS和HTML对页面中的交互能做的实在太少了,而那些还需要用代码来实现。比如一个链接要么是这个颜色,要么
- 栅格系统的形成1692年,新登基的法国国王路易十四感到法国的印刷水平强差人意,因此命令成立一个管理印刷的皇家特别委员会。他们的首要任务是设计
- 如何用Response.Write调用代替内嵌表达式?我们可以利用下面的代码,注意:代码的每一行对响应流有一次写操作,所有的代码都包含在一个
- 第一招、mysql服务的启动和停止net stop mysqlnet start mysql第二招、登陆mysql语法如下: mysql -
- ①.页面文件使用正确的编码,gb2312使用ANSI,utf-8使用utf-8; ②.ASP代码中设置正确的CODEPAGE,gb2312使
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN&
- 工作时常遇到需要在其它地方拷贝样式,比如Firebug之类的,但是复制出来的样式是带有换行和空格的,对于我这种有点洁癖的人来说,经常会一个个
- 是的,我们知道:我们可以为border设置它的width,这个border的宽度可以是5px,可是10px,可以是20px,可以是随意数值。
- 1.获取所有数据库名: SELECT Name FROM Master..SysDatabases ORDER BY Name2.获取所有表
- MySQL默认编码是latin1,因业务需要把它转到UTF8。1、导出数据导出表结构 mysqldump -d dataname >
- 当今越来越多的应用程序迁移到web平台上。由于没有平台的限制和安装的要求,SAAS的模式看起来非常有吸引力。Web应用程序的界面设计,其核心
- 在过去的十年中,MySQL已经成为广受欢迎的数据库,而WordPress博客使用的是MySQL数据库,虽然使用插件可以解决一些问题,但是实现
- 第一次做完主从库同步后正常,但工作过程中发现有一个库的数据库没有同步起来,在另外一个mysql(3307)中于是:1、在主库中创建一个临时库
- 本文从多个角度来讲解如何在Access数据库上如何上传并且显示上所上传图片。在 * 站制做过程中,需要上传图片、显示图片,上传的图片要能够保
- 动态联接库(DLL)是加快应用程序关键部分的执行速度的重要方法,但有一点恐怕大部分人都不知道,那就是在ASP文件也能通过调用DLL来加快服务