请问SQL大牛,该SQL语句会如何执行???

[a121984376]

[code=SQLSELECT] TOP (20)
[Extent1.[RoleName] AS [RoleName]
FROM ( SELECT [Extent1].[ID] AS [ID], [Extent1].[RoleName] AS [RoleName], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number]
FROM [dbo].[Role] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 20
ORDER BY [Extent1].[ID] ASC][/code]

[mycodeis0000]

这个有必要么？
虽然我也不清楚执行顺序~

[-晴天]

你有别名僻吗?
为什么不:

SELECT TOP 20 RoleName
FROM ( SELECT ID,RoleName, row_number() OVER (ORDER BY ID ASC) AS rn
FROM Role
)t
WHERE rn > 20 
ORDER BY ID

清清爽爽的多好!

这个程序先用函数 row_number 生成一个序列号,然后取序号大于20的前20个,即21~40个 RoleName.

[黄_瓜]

从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。虽然 SELECT 语句的完整语法较复杂，但其主要子句可归纳如下：

[ WITH <common_table_expression>]

SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression]

[ HAVING search_condition]

[ ORDER BY order_expression [ ASC | DESC ] ]

可在查询之间使用 UNION、EXCEPT 和 INTERSECT 运算符，以便将各个查询的结果合并或比较到一个结果集中。

Transact-SQL 语法约定

语法

<SELECT statement> ::=
[WITH <common_table_expression> [,...n]]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }

SELECT 语句的处理顺序
以下步骤显示 SELECT 语句的处理顺序。

1 FROM

2 ON

3 JOIN

4 WHERE

5 GROUP BY

6 WITH CUBE 或 WITH ROLLUP

7 HAVING

8 SELECT

9 DISTINCT

10 ORDER BY

11 TOP

[q465897859]

(6)   SELECT  (8) TOP (20) [Extent1.[RoleName] AS [RoleName] 
(4) FROM ( (2)SELECT  (3) [Extent1].[ID] AS [ID], [Extent1].[RoleName] AS [RoleName], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number] 
(1) FROM [dbo].[Role] AS [Extent1] 
)  AS [Extent1] 
(5) WHERE [Extent1].[row_number] > 20 
(7) ORDER BY [Extent1].[ID] ASC

按编号顺序

[q465897859]

(6)   SELECT  (8) TOP (20) [Extent1.[RoleName] AS [RoleName] 
(4) FROM ( (2)SELECT  (3) [Extent1].[ID] AS [ID], [Extent1].[RoleName] AS [RoleName], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number] 
(1) FROM [dbo].[Role] AS [Extent1] 
)  AS [Extent1] 
(5) WHERE [Extent1].[row_number] > 20 
(7) ORDER BY [Extent1].[ID] ASC

[jmx123456789]

( SELECT [Extent1].[ID] AS [ID], [Extent1].[RoleName] AS [RoleName], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number] 
FROM [dbo].[Role] AS [Extent1] 
)  AS [Extent1]

相当于 把数据查询出来(rownumber+其他列)放在临时表 Extent1

然后按条件查询 Extent1 

顺便向2楼学习

[jmx123456789]

[Quote=引用 2 楼 beirut 的回复:]
从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。虽然 SELECT 语句的完整语法较复杂，但其主要子句可归纳如下：
[/Quote]

学习

( SELECT [Extent1].[ID] AS [ID], [Extent1].[RoleName] AS [RoleName], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number] 
FROM [dbo].[Role] AS [Extent1] 
)  AS [Extent1]

先执行上面语句(rownumber+列组成的临时表) Extent1

俺条件查询 Extent1

[jmx123456789]

[Quote=引用 2 楼 beirut 的回复:]
从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。虽然 SELECT 语句的完整语法较复杂，但其主要子句可归纳如下：
[/Quote]

学习

( SELECT [Extent1].[ID] AS [ID], [Extent1].[RoleName] AS [RoleName], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number] 
FROM [dbo].[Role] AS [Extent1] 
)  AS [Extent1]

先执行上面语句(rownumber+列组成的临时表) Extent1

俺条件查询 Extent1

[大明湖畔_帅锅]

可以把条件一个一个取消加上 重复测试 应该可以看出其中的顺序