『原』ROW_NUMBER 与CTE

f q7SohV |@0有人问，如何取出排名20-30的人的信息。查了一下，发现ROW_NUMBER ()就可以解决。51Testing软件测试网*v7q [mp

7QQ!R5K~1L7}KW8c:wO#v0语法：
5QR"ZP&b
b7m0ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )51Testing软件测试网S;SQM*i4Y'un E
其中：PARTITION BY是将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。
1t+@Ywd*ke0例如：
6`3Ulx,ilT8dnO'G0SELECT SalesOrderID, ProductID, OrderQty
|?lv-__*@0    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
m C4m%f'o)h;Q r0    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
+d{"W&q-O0[ql;b$r0    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'51Testing软件测试网b+m/A Yj5j%[:o
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'51Testing软件测试网;H_QG.a};Hp%y
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
%bI!VBYi0FROM Sales.SalesOrderDetail
#G9qAL5N^9K&bO0WHERE SalesOrderID IN(43659,43664);
A#@l'\:i/D!f0GO51Testing软件测试网[8m9^9s;y"g$| O&T?*U

7y{bx.GJ|0例子:51Testing软件测试网 U:pR:s(\i

4G o6[4b Y(P2l,D(`M0 with a as (51Testing软件测试网,z/r5c@(G
 SELECT [ID]
&?D g$Rsd JO0      ,[Title]
K8W!q;t]
W0      ,[Type]51Testing软件测试网R LkXiB$m7Un s
      ,ROW_NUMBER() over (order by [ID]）row
0L3P x)]*a)k#`0  FROM [Vote].[dbo].[VM_VoteSubject]) 51Testing软件测试网B;T5L2H~3qmp)f
  select * from a 
VO{V"Yax+b
`0  where row between 5 and 851Testing软件测试网{s:c7x
F3chdll

6G5QB`hj'v(v-qV0说明:按ID排列,取第5到第8条的数据.51Testing软件测试网7TjfY[RI
其实跟下面语句结果一致:

 select *51Testing软件测试网'Uf6MzT'rlR1m
 from (SELECT [ID]51Testing软件测试网 @ Rm)Oj
      ,[Title]
K LXUM:a0      ,[Type]

c8D$w9`1T1Z!yF0      ,ROW_NUMBER() over (order by [ID]) row51Testing软件测试网W+Z'T9y#gTy~-CD6H:E
  FROM [Vote].[dbo].[VM_VoteSubject]) a
;K`#p8F1m9d1`p0  where row between 5 and 8

&bs7r\A)c0通过例子,发现了with...as...以前没见过.

.kd-Ns_ [7j0查找了一下,发现如下:51Testing软件测试网CW3J-c+wlJB7~+O

&uf Y1uv pp0指定临时命名的结果集，这些结果集称为公用表表达式 (CTE)。51Testing软件测试网;e:tbO#|G Z]O

P7YW(M^*A}0语法:51Testing软件测试网QoDKxg5_
 
#U3`-u?$W4Y0J@0[ WITH <common_table_expression> [ ,...n ] ]51Testing软件测试网 yun"T6~2t

<common_table_expression>::=51Testing软件测试网5nFi A}3[&v-{
        expression_name [ ( column_name [ ,...n ] ) ]51Testing软件测试网;U8@}oY;_ E#[
    AS51Testing软件测试网fo \!\9k
        ( CTE_query_definition )
(C"^w1X.\yS2q0n0
_cuH#?m0当然公用表表达式可以包括对自身的引用,这种表达式称为递归公用表表达式。51Testing软件测试网p`!@l+CF

:g9SN_ U{ x|0例如:

with menu ([ParentID],[ID],Menulevel) as
0LI6faI` T0(select a.[ParentID],a.[ID],0 as Menulevel51Testing软件测试网B/}.iO;U5a-M'B9V"L
from  [ESalesDB].[dbo].[BAS_Permission] a51Testing软件测试网5MQx3v9?MX.rY
where a.[ParentID]=051Testing软件测试网]:QV%z[ r6Xrz
Union ALL51Testing软件测试网^:cVFN | t
select e.[ParentID],e.[ID],Menulevel+151Testing软件测试网K;G3f
R ]qu L
from  [ESalesDB].[dbo].[BAS_Permission] e
2H0O/q9wO[*F2UL0INNER JOIN menu h51Testing软件测试网%s Z4G
Cf`e,Cr/Nj
ON e.[ParentID]=h.[ID]
}shY2I*|x0)51Testing软件测试网6gW
gt\5s.R
select [ID],[ParentID],Menulevel from menu

说明:取的是所有菜单、对应的父菜单以及其菜单级别。51Testing软件测试网RD X
u&Qx3e/BrV4i
L