开发笔记 第439页

如何高效查询表的总记录数?[总结-整理-马克]

首先想到的自然是在表主键上应用COUNT函数来查询了,这个是目前使用最多的方法,没有之一

SELECT COUNT(1) ROWS FROM product

这里再给出一些其它方法,这些方法或多或少会有一些限制,或者看起来不是那么”完美”,但依然有一定的借鉴意义,信息量不大,请客官慢用

另类方法1

充分利用系统自带的存储过程SP_SPACEUSED,详细的使用方式推荐谷哥或度娘,看代码

EXEC SP_SPACEUSED 'PRODUCT'

结果如下图所示

另类方法2

根据国际惯例,很多表都会设计一个步长为1的自增长整型(INT)列作为表主键,该做法优劣这里不展开讨论,仅基于这一”习惯”,外加一个假设:该字段连续无为断,则可以使用MAX函数了,限制较多哈

1,自增长列

2,该列连续

SELECT MAX(ProductCode) TotalCount FROM Product

输出

如果我有这样的自增主键表,可是不连续,那就不能用咯?其实您可以使用ROW_NUMBER()函数进行修复,再取MAX是一样的,为了不至于太水,这里仍然归为另类方法2,复习ROW_NUMBER的同学点这里

另类方法3

充分使用系统表中的信息,优点是速度够快!缺点是数据不精确,所以要结合您自身项目的特点来判定是否适合使用该方法,代码奉上

SELECT ROWS FROM [sysindexes] WHERE id = OBJECT_ID('product') and indid < 2

输出结果

另类方法3修复数据不精确方法,执行以下SQL脚本即可,请不要在生产环境使用!

DBCC UPDATEUSAGE(0, 'product') with COUNT_ROWS

该脚本执行后输出:DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

另类方法4,虚席以待,期待您的精彩点评!

上边介绍了一些系统表,系统存储过程,猜测您可能对下边的文章感兴趣

SQL SERVER 2008 函数大全 – 字符串函数

SQL2008系统统计函数

[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验

[MSSQL]ROW_NUMBER函数

[MSQL]RANK函数

[MSSQL]NTILE另类分页有么有?!

[MSQL]也说SQL中显示星期几函数

[MSSQL]COALESCE与ISNULL函数

[MSSQL]PIVOT函数

[MSSQL]FOR XML AUTO I

[MSSQL]FOR XML AUTO II

[MSSQL]TRY…CATCH…通用格式

如果您喜欢该博客请点击右下角推荐按钮,您的推荐是作者创作的动力!

——————————————
除非特别声明,文章均为原创,版权与博客园共有,转载请保留出处

支持图片

支持单个项目显示

2013-8-20

1.    SQL查询表的行列转换/小计/统计（with  rollup，with cube，pivot解析）

在实际的项目开发中有很多项目都会有报表模块，今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。

题目如下：

查询sales 和stores表，得出1993年每个store每季度销售数量及小计和总计，查询出的结果如下

其中sales表的数据结构如下：

其中stores表的数据结构如下：

1.1 普通方法（容易理解）

初看题目，第一感觉是竖表转横表，首先想到的是使用case when，

所以

第一步操作如下：

检索出结果如下：

这个时候由检索的结果可知，其中部分商店的统计信息没有合并统计，原因在于分组的时候我们是按商店名和日期分组的，

第二步操作，将第一步检索的信息，再次按店名分组统计，sql语句如下：

统计结果如下：

这个时候已经很接近标准答案了，但是还有一个统计行需要统计列出

第三步，将第二步统计的结果再和总计的结果Union一下就可以实现标准的结果

–对每个stor一年的销售明细进行汇总，之后按stor名分组

执行之后就可以得出我们想要的结果。

总结一下解题的整个思路，首先看题目要求求出每个店铺每年，每季度的销售统计，同时最后还要有总计行，统计全年/每个季度的销售总额。

接着通过case when语句查询出每个商店每年每季度的销售总统计，因为是按商店名和时间分组的，所以在查询出大体的数据结构之后，还需要再对结果进行按商店分组统计， 这样就统计出了符合答案要求的数据，最后在将统计出的结果与以结果为基础的再次统计union一下就得出了最终的答案。看起来很复杂的一个查询，只要把思 路理清之后一步一步实现就很容易了。

虽然我们经过查询实现了题目的要求，但是再让我们回过头来看看我们的查询语句，数据少的时候这样查询还没什么问题，但是如果数据量过大就会有很严重 的性能问题，同时，这样的sql查询语句过于庞大，有木有可以优化的方案呢？答案是肯定的。下面就给大家讲一下优化的查询解决方案。

1.2 With rollup  + case when count

首先我们的查询思路还是一下的，先用case when语句构建出大体的查询框架，唯一不同的是在group by 之后我们多了with rollup语句。代码如下：

在group by 之后加上with rollup，我们执行一下查询语句，就会发现马上出现了我们想要的结果，这是为什么呢？

在生成包含小计和合计的报表时，ROLLUP 运算符很有用。GROUP BY子句允许一个将额外行添加到简略输出端 WITH ROLLUP 修饰符。这些行代表高层(或高聚集)简略操作。ROLLUP 因而允许你在多层分析的角度回答有关问询的问题。或者你可以使用 ROLLUP, 它能用一个问询提供双层分析。将一个 WITH ROLLUP修饰符添加到GROUP BY 语句，使询问产生另一行结果，也就是在上例中采用rollup之后，在按stor_name分组之后，还能检索出本组类的整体聚合信息。

如果有多重分组列的情况时，ROLLUP产生的效果更加复杂。这时，每次在除了最后一个分类列之外的任何列出现一个 “break” (值的改变) ，则问讯会产生一个高聚集累计行。

1.3 With cube  +  povit

上例中我们讲了使用with rullup来实现统计分组，那么还木有比with rollup 更加简便的查询呢？答案是肯定的。

首先我们想按照商店和时间分组统计出每家商店每年/季度的销售情况，这个时候我们需要借助于with cube语句。代码如下：

执行结果如下：

With cube语句跟with rollup语句作用很相像，它们的区别在于with CUBE 生成的结果集显示了所选列中值的所有组合的聚合，而with ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合

第二步，我们将原始数据经过第一步的查询之后转换成了个标准的竖表，下边要做的就是如何将这个竖表转换成横表，我们在上边都是用case when的语法来实现这种表的横竖转换，这里我们换一种方式来实现。这里我们用povit方法来实现。代码如下：

上边代码示例中高亮部分即为使用pivot进行表的横竖转换的关键代码。

PIVOT用于行转列，在SQL Server 2000可以用聚合函数配合CASE语句实现，

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

这跟我们上边示例中使用的高亮标注的部分的方法是一样的

总结：

通过这样一个简单的查询，引出了今天要讲的表的行列转换（case when 和 pivot两种方法），表数据的统计（with rollup 和with cube方法），这也就达到了总结的目的。重要的不是讲这些方法怎么怎么用，主要是讲求解决问题的一个思路，以及在解决问题后对性能及效率的优化，希望可 以对大家有些帮助。

今天在博客园看到一篇文章，说是C#采集某某的数据，其实做采集小软件很久了，

用的最好的还是HtmlAgilityPack，真的没话说，非常方便快捷。

这里有详细介绍[.Net解析html文档类库HtmlAgilityPack完整使用说明–采集软件开发尤其好用].

本文不做详细说明了。主要还是上代码啊。毕竟业务应用才是我们开发人员努力编码的最终目的，

谁也不会为了单纯写点代码就自我满足了，满足业务需求才是主要的。

最近朋友叫我帮忙写一个小程序，获取某个网站的代理。也不怕大家知道了，就是这个网站

http://www.youdaili.cn/   牛逼吧？？好像你要批量的代理ip还得花钱买的。

但是现在我就告诉你不用花钱的方法，就是自己去复制，哈哈，开玩笑。

程序员都是懒人，谁会去复制，还是交给程序自动实现吧–那就是采集程序。

言归正传，今天就是主要介绍使用C#组件HtmlAgilityPack进行获取代理服务器ip和端口，

并手动设置到IE中的一个小程序。当然咯，开源哦。

代码萌点这里。百度网盘 http://pan.baidu.com/share/link?shareid=2118473016&uk=1765114824

流程很简单：打开主页，获取今天最新的代理网站地址，打开该链接，再去获取代理ip即可。

道理很简单，实现也很简单。

用程序打开网站主页http://www.youdaili.cn/  ，里面会有一个new的标示，但是我们不用这个标示，

我们用日期！这个最准确的。

我们可以看到08-19的最新代理就在那里。但是怎么让程序得到那？

首先我们可以用chrome的代码工具查看源代码，

我们想要得到的链接是在ul的li下面。

所有我们可以得到这样的正则表达式Xpath：//ul/li/a

对应程序代码就是：

大家可能注意到上面标红的地方说明很重要。主要是youdaili.cn的网站编码有点问题。只有这样才能得出gb2312的网页源代码。

然后就是上面所说的用的正则表达式了。

采集之后，我现在是保存到内存，还没有实际保存数据，各位自行解决数据保存问题吧。

采集之后的代理数据，怎么使用概不负责。haha。

转发注明来源哦：IT分享 http://suchso.com

回顾

《前端基于easyui的mvc扩展》、《前端基于easyui的mvc扩展(续)》 前两篇介绍了mvc内如何基于easyui进行扩展，在mvc里面有很多简化的语法可以让开发人员快速开发，既然我们已经对mvc进行了扩展，那么当然也 不能漏掉了基础的web form了。首先我们来比较一下mvc和web form，如果前端不使用razor，都是用aspx页面的话，使用的语法是一样的，只是mvc内提供了HtmlHelper、Model、 ViewData等来简化开发。

　　也就是说，如果我们使用如下的方式，其实在web form里面也是可行的，但是与mvc不同的是，web form内并没有像HtmlHelper的对象可以直接进行扩展，因此我们只能重新创建一个类来实现这些功能，既然无法使用类似HtmlHelper的对 象扩展，那么model对象也就必须在编码标签的时候手动传入了，大致代码如下：

<%User user =newUser{Name="测试",StateId=2}; %>
<%=EasyUIHelper.ValidateText(u => u.Name, user)%>
<%=EasyUIHelper.Datebox(u => u.Name, user)%>
<%=EasyUIHelper.Combobox<User>(u => u.StateId, user,"States","{ id: 'myCob', 'data-options': 'width: 100, editable: false' }")%>

实现

　　那么要达到以上的效果其实也不难，因为我们在前两篇已经进行了实现了，只要将其重构一下，使其可以支持在web form内工作便可以了，那么首先我们遇到的问题便是如何把表达式树转化为属性，这个主题我在《获取Lambda表达式内表达式的值》该文章中已经进行了讲解，调整后代码大致如下：

publicstaticstringValidateText(Expression<Func<TModel,object>> expression,TModel model,string attributeJson,Dictionary<string,string> attributes){string propertyName =PropertyHelper.ResloveName(expression);//关于获取表达式值的文章PropertyInfo property =typeof(TModel).GetProperty(propertyName);TagBuilder tag =newTagBuilder("input");
    //增加name、type属性ValidationAttribute[] validationAttrs;if(property.TryGetAttributes(out validationAttrs)){//请阅读转换规则代码}var extraAttributes =string.IsNullOrEmpty(attributeJson)? attributes :JsonHelper.Deserialize<IDictionary<string,string>>(attributeJson);//基于Newtonsoft转化json的公用方法//添加属性return tag.ToString();}

　　这样前端的代码就顺利转换完成了，剩下的便是如何让表单提交到服务端后，怎么样进行验证呢，大概的流程如下：

1. 将提交到服务端的表单直接转化为对应的实体类
2. 读取实体类各个属性以及属性内的验证特性，一一进行进行验证
3. 如果中间没有验证失败的话，那么就说明实体是符合验证要求的，可进行其他操作

　　第一步其实就是利用反射将属性相对应的值一一赋值给相应的属性而已，这里我们就不重复去实现了。

　　获取属性验证特性，我们可以通过扩展MemberInfo(因为PropertyInfo和MethodInfo都是MemberInfo的派生类)来获取特性，大致代码如下：

publicstaticboolTryGetAttributes(thisMemberInfo member,outTAttribute[] attributes,bool inherit =false)whereTAttribute:Attribute{
    attributes =null;var objs = member.GetCustomAttributes(typeof(TAttribute), inherit);if(objs.Length==0)returnfalse;

    attributes =Array.ConvertAll(objs, obj => obj asTAttribute);returntrue;}

　　对验证特性进行验证的代码大致如下：

//遍历属性ValidationAttribute[] validationAttributes =//获取验证特性foreach(var validation in validationAttributes){if(validation isRegularExpressionAttribute){var regularAttr = validation asRegularExpressionAttribute;var reg =newRegex(regularAttr.Pattern);if(value !=null&&!reg.IsMatch(value.ToString()))returnfalse;}else{ValidationResult result = validation.GetValidationResult(value,newValidationContext(instance,null,null));if(result !=ValidationResult.Success)returnfalse;}}

　　这里之所以要将RegularExpressionAttribute 单独抽出来判断是因为System.ComponentModel.DataAnnotations.dll(v4.0.0.0)内的实现有BUG，没有 根据正则正确的实现GetValidationResult方法。

效果

//实体
public class User
{
    private string m_Name = null;
    [Required(ErrorMessage = "用户名不能为空!")]
    [StringLength(10, MinimumLength = 4, ErrorMessage = "用户名长度必须在4-10之间")]
    [DisplayName("用户名：")]
    public string Name
    {
        get { return m_Name; }
        set { m_Name = value; }
    }
    //略
}

//前端
<% 
    FormBuilder form = new FormBuilder();
    User user = new User { Name = "测试", StateId = 2 }; %>
<%=form.CreateBeginHtml("/Handlers/MvcHandler.ashx", "fmTest")%>
<%=EasyUIHelper.ValidateText(u => u.Name, user)%>
<br />
<%=EasyUIHelper.Combobox(u => u.StateId, user, "States", "{ id: 'myCob', 'data-options': 'width: 100, editable: false'}")%>
<br />
<input type="submit" value="提交" />
<%=form.CreateEndHtml() %>
<script type="text/javascript">
$('#fmTest').easyuiForm();
</script>

结尾

　　谢谢大家，如文章有任何问题和错误请指出，谢谢！

做过页面布局的同学对z-index属性应该是很熟悉了，z-index是针对网页显示 中的一个特殊属性。因为显示器是显示的图案是一个二维平面，拥有x轴和y轴来表示位置属性。为了表示三维立体的概念如显示元素的上下层的叠加顺序引入了 z-index属性来表示z轴的区别。表示一个元素在叠加顺序上的上下立体关系。

z-index值较大的元素将叠加在z-index值较小的元素之上。对于未指定此属性的定位对象，z-index 值为正数的对象会在其之上，而 z-index 值为负数的对象在其之下。

简单演示

<div style="width:200px;height:200px;background-color:#0e0;"></div>
    <div style="position:relative; top:-50px; width:100px;height:100px;background-color:#00e;"><div>

两个DIV，第二个向上移动50px，正常情况应该是这样的

第二个div遮住了第一个div，对第二个添加z-index属性

<div style="width:200px;height:200px;background-color:#0e0;"></div>
    <div style="position:relative; top:-50px; width:100px;height:100px;background-color:#00e;z-index:-5;"><div>

结果就会变成这个样子，z-index 最简单的应用就是这样

只对定位元素有效

z-index属性适用于定位元素（position属性值为 relative 或 absolute 或 fixed的对象），用来确定定位元素在垂直于显示屏方向（称为Z轴）上的层叠顺序，也就是说如果元素是没有定位的，对其设置的z-index会是无效的。

<div style="width:200px;height:200px;background-color:#0e0;z-index:30"></div>
<div style="position:relative; top:-50px; width:100px;height:100px;background-color:#00e;z-index:10;"><div>

虽然第一个div的z-index比第二个div大，但是由于第一个div未定位，其z-index属性未起作用，所以仍然会被第二个div覆盖。

相同z-index谁上谁下

相同的z-index其实有两种情况

1.如果两个元素都没有定位发生位置重合现象或者两个都已定位元素且z-index相同发生位置重合现象，那么按文档流顺序，后面的覆盖前面的。

<div style="position:relative;width:200px;height:200px;background-color:#0e0;"></div>
<div style="position:relative; top:-50px; width:100px;height:100px;background-color:#00e;"><div>

2.如果两个元素都没有设置z-index，使用默认值，一个定位一个没有定位，那么定位元素覆盖未定位元素

<div style="position:relative;top:50px;width:200px;height:200px;background-color:#0e0;"></div>
<div style=" width:100px;height:100px;background-color:#00e;"><div>

父子关系处理

如果父元素z-index有效，那么子元素无论是否设置z-index都和父元素一致，会在父元素上方

<div style="position:relative;width:200px;height:200px;background-color:#0e0;z-index:10;">
        <div style="position:relative;width:100px;height:100px;background-color:#00e;z-index:-5;"><div>
    </div>

虽然子元素设置z-index比父元素小，但是子元素仍然出现在父元素上方

如果父元素z-index失效（未定位或者使用默认值），那么定位子元素的z-index设置生效

<div style="position:relative;width:200px;height:200px;background-color:#0e0;">
        <div style="position:relative;width:100px;height:100px;background-color:#00e;z-index:-5;"><div>
</div>

子元素z-index=-5生效，被父元素覆盖

兄弟之间子元素

如果兄弟元素的z-index生效，那么其子元素覆盖关系有父元素决定

<div style="position:relative;width:100px;height:100px;background-color:#0e0;z-index:5;">
        <div style="position:relative;width:50px;height:250px;background-color:#00e;z-index:50;"></div>
    </div>

    <div style="position:relative;width:100px;height:100px;background-color:#0e0;z-index:10;margin-top:4px;">
        <div style="position:relative;width:30px;height:150px;background-color:#e0e;z-index:-10;"></div>
    </div>

虽然第一个div的子元素的z-index比较高，但是由于其父元素z-index比第二个div低，所以第一个div子元素会被第二个div及其子元素覆盖

应用

经常会有这样一种错误在table中最后各行一个td放一个div，点击弹出子菜单做一些删除、修改什么的操作，但是每次弹出的菜单都会被下面各行的div覆盖

<!DOCTYPE html>
<html>
    <head>
        <title>Test</title>
        <style type="text/css" >
            html,body
            {
                height:100%;
                width:100%;
                padding:0;
                margin:0;
            }

            .menu
            {
                background-color:#0e0;
                position:relative;
                z-index:10;
            }

            .options
            {
                display:none;
                position:absolute;
                top:
                z-index:30;
            }

            .options div
            {
                background-color:#00e;
            }
        </style>
    </head>
    <body>
    <table border="1" cellpadding="4px" cellspacing="0">
        <tr>
            <th>Name</th>
            <th>Age</th>
            <th>Options</th>
        </tr>
        <tr>
            <td>Byron</td>
            <td>24</td>
            <td>
                <div class="menu" >
                    <div>Options</div>
                    <div class="options" style="display:block;position:absolute;top:20px;">
                        <div>Opion1</div>
                        <div>Opion2</div>
                        <div>Opion3</div>
                        <div>Opion4</div>
                    </div>
                </div>
            </td>
        </tr>
        <tr>
            <td>Byron</td>
            <td>24</td>
            <td>
                <div class="menu">
                    <div>Options</div>
                    <div class="options" >
                        <div>Opion1</div>
                        <div>Opion2</div>
                        <div>Opion3</div>
                        <div>Opion4</div>
                    </div>
                </div>
            </td>
        </tr>
        <tr>
            <td>Byron</td>
            <td>24</td>
            <td>
                <div class="menu">
                    <div>Options</div>
                    <div class="options" >
                        <div>Opion1</div>
                        <div>Opion2</div>
                        <div>Opion3</div>
                        <div>Opion4</div>
                    </div>
                </div>
            </td>
        </tr>
    </table>
    </body>
<html>

期望样式

实际样式

这时候习惯于增大options 的z-index却发现于事无补，为什么呢？因为每个menu的z-index相同，它们的层叠顺序按文档流顺序，无论子元素z-index调到多大，上 面menu的options还是会被下面menu遮盖。这时候我的做法一般是把options放到外面，所有的menu用一个，使menu与 options没有父子关系，或者干脆在点击menu的时候把它的z-index调大，这样其子元素就不会被遮盖住了。

最后

本文的例子都是以符合W3C的Chrome浏览器做验证，但在IE6，7 z-index的默认值并不是auto而是0，这样会导致很多奇怪现象，这时候就需要考虑这点了。