sql2012 新增 OFFSET/FETCH

通过were和having条件可以对数据进行筛选,那么如何通过排序对数据进行筛选呢?

SQL Server 对行数的排序提供了 TOP 筛选。SQL Server 2012 新增了 OFFSET
筛选。

需求:查询表dbo.Message,每页10条,查询第2页

1.TOP筛选

 

1:TOP()

用于限制查询返回行数或者行数的百分比。

一、TOP 筛选

SELECT TOP(20) * FROM  dbo.Message WHERE Code NOT IN
(SELECT TOP(10) Code FROM dbo.Message)

例如 我们对订单表筛选最近产生的订单5条

  如果有 ORDER BY 子句,TOP
筛选将根据排序的结果返回指定的行数。如果没有 ORDER BY 子句,TOP
筛选将按照行的物理顺序返回指定的行数。

2:BETWEEN *  AND *  ,  Row_Number() OVER(ORDER BY *) AS rowNum 

SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

 

SELECT *,ROW_NUMBER() OVER(ORDER BY Code) AS rowNum INTO #a FROM  dbo.Message 
SELECT * FROM #a WHERE rowNum BETWEEN 11 AND 20
DROP TABLE #a;

这是通过 排序对数据进行筛选

1. 返回指定数目的行

或者: 

前面写过order by子句是在select 子句之后计算的, select又包含disinct选项
,所以dinsinct 计算顺序大于top 在对数据去重复后再进行top计算

  TOP 用于指示从查询结果集返回指定数目的行。

SELECT * FROM (
                SELECT  *, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS 'RowNumberForSplit'
                FROM  office.Conference 
                WHERE  1=1) temp 
WHERE RowNumberForSplit BETWEEN 1 AND 10

应该注意的是  一个sql语句出现 top 和order by时 order by 是被计算了两遍  
第一次 先对数据进行显示排序  第二次为TOP定义筛选。

  例如,返回前2行记录:

 

搞不懂一点的是TOP查询返回的是表结果还是游标?, 一般带order
by子句的查询返回是一个游标,而不是一个关系结果。

    SELECT TOP (2) ColumnA, ColumnB  FROM Table1

3:WITH * AS ()   ,    Row_Number() OVER(ORDER BY *) AS rowNum 

TOP选项中加入PERCENT关键字 寻找最近产生1%订单

 

WITH sss AS(
SELECT *,ROW_NUMBER() OVER(ORDER BY CreateTime) AS rowNum FROM DBO.Message
)
SELECT * FROM sss WHERE rowNum BETWEEN 11 AND 20
SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

2. 返回指定百分比的行

4: SQL Server 2012的OFFSET-FETCH筛选

以日期作为排序字段 会出现 多行具有相同的订单日期
每次执行都有不确定性会返回不一样的结果,所有要确保排序字段的唯一性。

  可以使用百分比,如果遇到百分比的计算结果不是整数,将向上舍入(即“进一法”,而不是“四舍五入”或“截尾取整”)。例如,返回前10%的行:

SELECT * FROM DBO.Message ORDER BY CreateTime OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

问题来了 如果问题已经存在不能确保唯一性 那如何解决呢? 我们可以加入WITH
TLES选项 主要作用返回表中
最后匹配一行所有结果防止因为行同源导致返回数据不一致。

    SELECT TOP (10) PERCENT ColumnA, ColumnB FROM Table1

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图