前面写过order by子句是在select 子句之后计算的,

作者: www.9159.com  发布:2019-10-01

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

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

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

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

1.TOP筛选

 

1:TOP()

1:TOP()

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

一、TOP 筛选

SELECT TOP(20) * FROM  dbo.Message WHERE Code NOT IN
(SELECT TOP(10) Code FROM dbo.Message)
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 

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;
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
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 

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
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筛选

4: SQL Server 2012的OFFSET-FETCH筛选

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

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

SELECT * FROM DBO.Message ORDER BY CreateTime OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
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

 

 

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

 

这里第4中方法做详细介绍:

这里第4中方法做详细介绍:

注意:即使没有排序order by 子句 TOP选项排序也是不明确的,SQL Server 会返回首先物理访问到N 行数据 而不是根据表顺序。

3.WITH TIES 参数

 

 

2.OFFSET-FETCH 筛选

  在与ORDER BY 子句组合使用时,有时候会出现并列排名的情况,例如,返回前10名优秀成绩的学生,可能遇到多名学生并列第10名。此时需要指定 WITH TIES,以确保并列第10名的学生都被包含到结果集中,此时的结果集可能多于10行。示例:

TOP选项是一个非常实用的筛选类型,但它有两个缺陷——不是标准SQL,且不支持跳过功能。标准SQL定义的TOP类似筛选称为OFFSET-FETCH,支持跳过功能,这对针对特定页面的查询非常有用。SQL Server2012引入了对OFFSET-FETCH筛选的支持。

TOP选项是一个非常实用的筛选类型,但它有两个缺陷——不是标准SQL,且不支持跳过功能。标准SQL定义的TOP类似筛选称为OFFSET-FETCH,支持跳过功能,这对针对特定页面的查询非常有用。SQL Server2012引入了对OFFSET-FETCH筛选的支持。

可以认为加强版 TOP 功能  OFFSET-FETCH 子句被视为order by 子句的一部分   offset 子句 跳过多少行,  fetch 子句显现多少行

  SELECT TOP (10) WITH TIES ColumnA, ColumnB FROM Table1 ORDER BY ColumnA DESC

SQL Server 2012中的OFFSET-FETCH筛选被视为ORDER BY子句的一部分,通常用于实现按顺序显示效果。OFFSET子句指定要跳过的行数,FETCH子句指定在跳过的行数后要筛选的行数。请思考一下下面的查询示例。

SQL Server 2012中的OFFSET-FETCH筛选被视为ORDER BY子句的一部分,通常用于实现按顺序显示效果。OFFSET子句指定要跳过的行数,FETCH子句指定在跳过的行数后要筛选的行数。请思考一下下面的查询示例。

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

 

SELECT orderid, orderdate, custid, empid

SELECT orderid, orderdate, custid, empid

以上是跳过50行 显示25行 

二、OFFSET 筛选

FROM Sales.Orders

FROM Sales.Orders

OFFSET-FETCH 子句必须在 orderd by 后面     可以只要有 fethch   但没有fetch 是不行的

  OFFSET 子句必须与 ORDER BY 子句组合使用,而且不可以与 TOP 同时使用。与 TOP 相比,OFFSET 即没有 PERCENT 参数,也没有 WITH TIES 参数。

ORDER BY orderdate, orderid

ORDER BY orderdate, orderid

缺点是 不支持PERCENT和WITH TIES  选项 而TOP 支持

 

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

建议 如果只使用筛选行数功能 请使用 OFFSET-FETCH  因为它是标准SQL 而TOP不是 除非需要PERCENT和WITH TIES  选项 仅仅只是筛选行数不建议用TOP

1. 跳过指定的行数

此查询按orderdateorderid顺序(订单日期从最远到最近,并添加了决胜属性(tiebreaker)orderid)排序Orders表中的行。基于此顺序,OFFSET子句跳过前50行,由FETCH子句仅筛选下面的25行。

此查询按orderdateorderid顺序(订单日期从最远到最近,并添加了决胜属性(tiebreaker)orderid)排序Orders表中的行。基于此顺序,OFFSET子句跳过前50行,由FETCH子句仅筛选下面的25行。

 

  OFFSET 子句指定在从查询表达式中开始返回行之前,将跳过的行数。OFFSET 子句的参数可以是大于或等于零的整数或表达式。ROW 和 ROWS 可以互换使用。例如:

请注意,使用OFFSET-FETCH的查询必须具有ORDER BY子句。此外,FETCH子句不支持没有OFFSET子句。如果你不想跳过任何行,但是希望使用FETCH筛选,你应当使用OFFSET 0 ROWS来表示。不过,没有FETCHOFFSET是允许的,这种情况是跳过指定的行数,并返回查询结果中所有剩余行。

请注意,使用OFFSET-FETCH的查询必须具有ORDER BY子句。此外,FETCH子句不支持没有OFFSET子句。如果你不想跳过任何行,但是希望使用FETCH筛选,你应当使用OFFSET 0 ROWS来表示。不过,没有FETCHOFFSET是允许的,这种情况是跳过指定的行数,并返回查询结果中所有剩余行。

  SELECT ColumnA, ColumnB FROM Table1 ORDER BY ColumnA OFFSET 10 ROWS

OFFSET-FETCH语法有一些有趣的语言方面需要注意。单数格式ROW和复数格式ROWS是可以互换的,此举是让你能够以直观的类似英语方式来描述筛选。例如,假设你仅希望获取一行,如果你指定了FETCH 1 ROWS,虽然这在语法上是有效的,不过看上去会很怪。因此,你可以使用FETCH 1 ROW格式。此互换同样适用于OFFSET子句。另外,如果你不希望跳过任何行(OFFSET 0 ROWS),你可能觉得“first”比“next”更合适,因此,FIRST 和NEXT格式是可以互换的。

OFFSET-FETCH语法有一些有趣的语言方面需要注意。单数格式ROW和复数格式ROWS是可以互换的,此举是让你能够以直观的类似英语方式来描述筛选。例如,假设你仅希望获取一行,如果你指定了FETCH 1 ROWS,虽然这在语法上是有效的,不过看上去会很怪。因此,你可以使用FETCH 1 ROW格式。此互换同样适用于OFFSET子句。另外,如果你不希望跳过任何行(OFFSET 0 ROWS),你可能觉得“first”比“next”更合适,因此,FIRSTwww.9159.com , 和NEXT格式是可以互换的。

 

如你所见,从支持跳过功能看,OFFSET-FETCH子句比TOP子句更灵活。不过,OFFSET-FETCH 不支持PERCENTWITH TIES选项,而TOP支持。由于OFFSET-FETCH是标准的,而TOP不是,我建议使用OFFSET-FETCH作为你的默认选择,除非你需要TOP支持且OFFSET-FETCH不支持的功能。

如你所见,从支持跳过功能看,OFFSET-FETCH子句比TOP子句更灵活。不过,OFFSET-FETCH 不支持PERCENTWITH TIES选项,而TOP支持。由于OFFSET-FETCH是标准的,而TOP不是,我建议使用OFFSET-FETCH作为你的默认选择,除非你需要TOP支持且OFFSET-FETCH不支持的功能。

2. 跳过指定的行数,再返回指定的行数

 

 

  FETCH 子句不可以单独使用,必须跟在 OFFSET 子句之后。

  FETCH 子句指定在处理 OFFSET 子句后,将返回的行数。FETCH 子句的参数可以是大于或等于 1 的整数或表达式。例如:

  SELECT ColumnA, ColumnB FROM Table1 ORDER BY ColumnA OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

 

3. 参数互换

(1)ROW 和 ROWS 可以互换使用

  “1 ROWS”的表述虽然 SQL Server 的语法,但是不符合英文语法,因此,ROW 和ROWS 可以互换,例如“1 ROW”。

 

(2)FIRST 和 NEXT 可以互换使用

  遇到“OFFSET 0 ROWS”时(即不跳过任何行),“FETCH NEXT 5 ROWS ONLY”的表述看起来不太自然,因此,可以换为“FETCH FIRST 5 ROWS ONLY”。

 

4. 行数的表达式

  行数可以使用返回整数值的任何算术、常量或参数表达式,但不可以使用标量子查询。

本文由9159.com发布于www.9159.com,转载请注明出处:前面写过order by子句是在select 子句之后计算的,

关键词:

上一篇:没有了
下一篇:没有了