mongodb的实体转成json后

在页面上 _id和Datetime会显示错误.

用javascript把Datetime格式化的方法是

var time = parseFloat(row.CreateTime.match(/(\d)+/g)[0]);
                       time = new Date(time);
                       return Tools.ConvertJsTimeToLongDate(time);

用javascript把BsonObjectID转成string的方法未知

Apply运算符支持两种形式

cross apply和outer apply

apply运算符对两个输入表进行操作, 其中第二个可以是表表达式, 我们将他们分别称为左表和右表.

右表通常是一个表表达式或者内联表值函数.

                

cross apply运算符实现了一个逻辑查询处理步骤: 把右表表达式应用到左表的每一行, 再把结果集组合起来, 生成一个统一的结果表.

select * from Sales.Shippers s cross join HR.Employees e

select * from Sales.Shippers s cross apply HR.Employees e

这两条语句生成的结果是一样的. 和连接不同的是, 使用cross apply操作符的时候, 对于左表中的每一行, 右表表达式可以代表不同的数据行集合. 理解上面加粗的那句话就是, 在右表中的查询可以引用左表的列: 如果右表是个派生表, 可以引用左表列; 如果右表是个内联表值函数, 可以将左表的列作为参数进行传递

/*返回每个客户最新的三个订单*/

select c.custid,t.orderid,t.orderdate from Sales.Customers c cross apply 

    (select top 3 * from Sales.Orders o where o.custid= c.custid order by o.orderdate desc) t

如果使用outer apply, 那么将会返回左表列在右表表达式为空集的数据.

递归CTE

declare @dt table(id int, pid int)

insert into @dt

select 1,0 union

select 2,0 union

select 3,1 union

select 4,1 union

select 5,3;

with t as(

    select id,pid from @dt where id=1/*第一次结果集*/

    union all

    select d.id,d.pid from @dt d inner join t

    on d.pid=t.id/*用第二个结果集的父ID去匹配第一个结果集的ID, 直到出现空集合位置*/

/*查询部门为1下的所有子部门.*/

)

select * from t

另一个例子

with t as(

select e.empid,e.mgrid,e.firstname,e.lastname from HR.Employees e where empid=9

union all

select e2.empid,e2.mgrid,e2.firstname,e2.lastname from HR.Employees e2

inner join t on e2.empid=t.mgrid

)

select * from t

/*查询empid为9的所有上级*/

具体见sql server 2008技术内幕 t-sql语言基础 171页

首先, 我们确定Sql语句执行大致流程

• FROM   =>> WHERE =>> GROUP BY =>> HAVING =>> SELECT ==>

•             { Over

•                Distinct

•                Top} ==>

• ORDER BY

其中比较难一点的就是Over窗口函数了.

Over单独配合聚合函数

SELECT * FROM Sales.OrderValues;

有表如图1

SELECT orderid, custid, val,orderdate

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue

FROM Sales.OrderValues;

图2

对比上图, 发现除了增加了两列外, 没有改变排序.

SELECT orderid, custid, val,orderdate

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue,

  SUM(val) OVER(PARTITION BY custid) AS custtotalvalue

  /*,AVG(val) over(PARTITION BY orderdate) as custAvgValue*/

FROM Sales.OrderValues;

图3

对比图1. 发现, Partition By 不仅让sum聚合函数只计算CustID区域的val. 并且使得结果按custid排序

SELECT orderid, custid, val,orderdate

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue

  ,SUM(val) OVER(PARTITION BY custid) AS custtotalvalue

  ,AVG(val) over(PARTITION BY orderdate) as custAvgValue

FROM Sales.OrderValues;

图4

对比图3, 发现如果再增加一个有partitionby子句的窗口函数, 结果按照后一个(这里是orderdate)排序

SELECT orderid, custid, val,orderdate,ROW_NUMBER() over(order by val desc)

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue

  ,SUM(val) OVER(PARTITION BY custid) AS custtotalvalue

  /*,AVG(val) over(PARTITION BY orderdate) as custAvgValue*/

  FROM Sales.OrderValues;

结果同图4一样. 虽然增加了一列RowIndex列, 但是结果依然是按照custid排序, 除非将那一句放到后面.

如果排序函数中使用了partition by子句. 那么, 返回的结果还是以partition by子句的字段升序.

 

当然, 以上结果是在没有order by排序的前提下. 如果最外层有order by, 那么还是按order by 排序.

with ties

WITH TIES

指定从基本结果集中返回额外的行，对于 ORDER BY 列中指定的排序方式参数，这些额外的返回行的该参数值与 TOP n (PERCENT) 行中的最后一行的该参数值相同。只能在 SELECT 语句中且只有在指定了 ORDER BY 子句之后，才能指定 TOP...WITH TIES。

注意：返回的记录关联顺序是任意的。ORDER BY 不影响此规则

来源：MSDN，http://msdn.microsoft.com/zh-cn/library/ms189463.aspx

通俗解释

如果按照order by 参数排序TOP n（PERCENT）返回了前面n（pencent）个记录，但是n+1…n+k条记录和排序后的第n条记录的参数值（order by 后面的参数）相同，则n+1、…、n+k也返回。n+1、…、n+k就是额外的返回值。

举例说明

declare @tb table(tname varchar(10), score int)

insert into @tb select 'a',80

insert into @tb select 'b',80

insert into @tb select 'c',70

insert into @tb select 'd',60

insert into @tb select 'e',50

insert into @tb select 'f',40

insert into @tb select 'g',40

insert into @tb select 'h',30

insert into @tb select 'i',80

insert into @tb select 'j',70

select top 4 * from @tb order by score desc

返回结果如下.  但是我们发现 j的分数也是70, 这个时候我们需要统计j的时候, with ties就拍上用场了.

select top 4 with ties * from @tb order by score desc

OVER子句用于为行为定义一个窗口（windows），以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。例如，聚合函数和排名函数都是可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也称之为开窗函数。

简单的理解, 就是over提供了任意的聚合. 在一般情况下, 如果我们要用到聚合函数, 需要先使用groupby分组. 用了over之后, 某些情况就简单很多.

如果over()里面没有子句, 那么, over子句前面的聚合函数的操作窗口(可操作的数据行集合)为该次查询返回的所有行.  over子句提供partition by进行分区. 其实就是把操作窗口(可操作的数据行集合)进行分组, 并匹配出符合分组参数的行集合. 那么我们来看例子.

某表如下, 订单Id, 所属客户, 和价值val.

我们想在后面加上几列数据以更好地支持我们的显示系统. totalValue(查询的所有价值之和), avgValue(查询的所有价值平均数),custAvgValue(某客户的订单价值之和),custTotalValue(某客户的订单价值平均)

期望得到如图

declare @dt datetime;set @dt=GETDATE()

SELECT orderid, custid, val,

  SUM(val) OVER() AS totalvalue,--所有行作为操作值

  AVG(val) over() as avgvalue,--所有行作为操作值

  AVG(val) over(PARTITION BY custid) as custAvgValue,--按客户ID进行分区, 并自动抽取出符合当前行的分区参数(这里是custid)的数据行

  SUM(val) OVER(PARTITION BY custid) AS custtotalvalue--按客户ID进行分区, 并自动抽取出符合当前行的分区参数(这里是custid)的数据行

FROM Sales.OrderValues;

--同样的效果, 用老式的方式进行查询

select DATEDIFF(ms,@dt,getdate())

set @dt=GETDATE()

SELECT orderid, custid, val,

  (select SUM(val) from sales.OrderValues) AS totalvalue,

  (select avg(val) from sales.OrderValues) as avgvalue,

  (select avg(val) from sales.OrderValues where custid=s.custid) as custAvgValue,

  (select sum(val) from sales.OrderValues where custid=s.custid) AS custtotalvalue

FROM Sales.OrderValues s;

select DATEDIFF(ms,@dt,getdate())

--表的数据为800多行. 第一种方式13-20ms. 第二种方式耗时40-50ms. 大数据量可见效率之差

partition by 和 order by连用.

sqlserver2005之后, 有一个排序函数, row_number() over(order by XX).

准备脚本

declare @Student table  --学生成绩表

(

 id int,  --主键

 Grade int, --班级

 Score int --分数

)

insert @Student 

    select 1,1,88

union all select 2,1,66

union all select 3,1,75

union all select 4,2,30

union all select 5,2,70

union all select 6,2,80

union all select 7,2,60

union all select 8,3,90

union all select 9,3,70

union all select 10,3,80

--不分年级按学生成绩排名

select *,ROW_NUMBER() over(order by Score desc) as Sequence from @Student

--分年级按学生成绩排名,并取得年级平均成绩

select *, ROW_NUMBER() over(partition by grade order by score desc) as sequence, 

        AVG(score*1.0) over(partition by grade) gradeAvgScore from @Student

 

关于over子句和其他排名函数的使用请参考, 相当不错哦http://www.cnblogs.com/tylerdonet/archive/2011/07/08/2101384.html

在mvc中的使用见http://www.cnblogs.com/feelboy/archive/2011/07/02/2096146.html

在asp.net项目中使用DataAnnotations 验证 见http://www.cnblogs.com/hjf1223/archive/2010/11/07/independent-dataannotation-validation.html

在我的类库Lib.ClassExt中集成了上面大侠提供的扩展类. 使用方法如下

在webform页面中构建表单:

<input type="text" name="UserName" /><br />
        <input type="text" name="Password" /><br />
        <asp:Button ID="btn" runat="server" Text="ssss" onclick="btn_Click" />

model类引用using System.ComponentModel.DataAnnotations;命名空间, 代码如下:

public class RegModel

    {

        [Required(AllowEmptyStrings=false, ErrorMessage="用户名啊不能空啊")]

        public string UserName { get; set; }

        [Required(AllowEmptyStrings = false, ErrorMessage = "年龄啊,不能空啊")]

        [RegularExpression(@"^[0-9a-zA-Z]+$",ErrorMessage="只能是数字!!")]

        public string Password { get; set; }

    }

后台代码如下

protected void btn_Click(object sender, EventArgs e)

        {

            Model.RegModel model = new Model.RegModel { Password = Request.Form["Password"], UserName = Request.Form["username"] };

            foreach (var item in model.IsValid<Model.RegModel>())

            {

                Response.Write(item.FieldName + "  " + item.Message + "<br>");

            }

        }