SQL多表查询中的分页
SQL多表查询中的分页，字段组合综合实例解析
综合实例：
SELECT*
FROM(
SELECT ROW_NUMBER()OVER(ORDER BY U.FUsersSort ASC)AS Row,
U.*,D.FName AS DepartmentName,CD.FName AS DutyName,TB.FRoleIDs,TQ.FRoleNames FROM CY_Users U
JOIN(
SELECT*FROM(SELECT DISTINCT FID FROM CY_Users) A OUTER APPLY(
SELECT FRoleIDs=STUFF(REPLACE(REPLACE(
(SELECT FRoleID FROM CY_UserHaveRole N WHERE FUserID = A.FID
FOR XML AUTO),'
)TN
) TB on U.FID =TB.FID
JOIN(
SELECT*FROM(SELECT DISTINCT FID FROM CY_Users) B OUTER APPLY(
SELECT FRoleNames =STUFF(REPLACE(REPLACE(
(SELECT T.FName FROM CY_Roles T JOIN CY_UserHaveRole UHR ON T.FID = UHR.FRoleID
where UHR.FUserID =B.FID FOR XML AUTO),'
)TM
)TQ ON U.FID =TQ.FID
LEFT JOIN CY_Department D ON U.FDepartmentID = D.FID
LEFT JOIN CY_Duty CD ON U.FDutyID = CD.FID
)AS PagedResults
WHERE Row BETWEEN 0 AND 25 --WHERE Row >= 0 AND Row <= 25
实例说明：
功能：查询用户的基本信息（除了CY_Users中的字段外加:部门名，职务名，角色ID号和名称（一
个用户可能有多个角色，要求用逗号分隔显示））
字段组合：每个用户的多个角色名称用逗号隔开作为一个字段（角色名）显示，角色ID也做同样处理涉及到的表：CY_Users、 CY_Department、 CY_Roles 、CY_UserHaveRole、 CY_Duty 表之间的关系：CY_Users 和CY_Roles是多对多的关系，CY_UserHaveRole是其关系表
分页：根据用户表中的排名字段FSort的从小到大的顺序取某一页的数据（这里取第一页25条数据）
解析：（备注：解析参考自MSDN和网络资料）
REPLACE函数：
REPLACE (string_expression1,string_expression2,string_expression3 )
功能：用第三个字符串表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式
string_expression:可以是字符或二进制数据类型
示例：
以下示例使用xxx替换abcdefghi中的字符串cde。
SELECT REPLACE('abcdefghicde','cde','xxx');
GO
下面是结果集：
------------
abxxxfghixxx
(1 row(s) affected)
STUFF函数：
STUFF ( character_expression , start , length ,character_expression )
功能：删除指定长度的字符并在指定的起始位置插入另一个字符
注意：start=1表示起始位置是从1开始，（而不是通常编程中的0表示开始）看以下示例就明白了
示例：
以下示例在第一个字符串abcdef中删除从第2个位置（字符b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串。
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO
下面是结果集：
---------
aijklmnef
(1 row(s) affected)
使用FOR XML构造XML
实例中巧用了一个FOR XML AUTO来生成特定XML的格式，即将SELECT查询出的行集生成XML
格式。下面的例子展示FOR XML AUTO生成的格式
例：
select FRealName, R.FName from CY_UserHaveRole UHR
join CY_Roles R on R.FID = UHR.FRoleID
join CY_Users U on U.FID = UHR.FUserID
where U.FName='xuzhihong'
for xml auto
结果：
即：用户名为xuzhihong的真实姓名为测试用户XZH，该用户有三个角色：普通用户、管理员、高级用
户
在 FOR XML 子句中，指定以下模式之一：
?RAW
?AUTO
?EXPLICIT
?PATH
RAW 模式将为 SELECT 语句所返回行集中的每行生成一个
AUTO 模式将基于指定 SELECT 语句的方式来使用试探性方法在 XML 结果中生成嵌套。您对生成的 XML 的形状具有最低限度的控制能力。除了 AUTO 模式的试探性方法生成的 XML 形状之外，还可以编写 FOR XML 查询来生成 XML 层次结构。
EXPLICIT 模式允许对 XML 的形状进行更多控制。您可以随意混合属性和元素来确定 XML 的形状。由于执行查询而生成的结果行集需要具有特定的格式。此行集格式随后将映射为 XML 形状。使用EXPLICIT 模式能够随意混合属性和元素、创建包装和嵌套的复杂属性、创建用空格分隔的值（例如OrderID 属性可能具有一列排序顺序 ID 值）以及混合内容。
但是，编写 EXPLICIT 模式的查询会比较麻烦。可以使用某些新的 FOR XML 功能（例如编写嵌套 FOR XML RAW/AUTO/PATH 模式查询和 TYPE 指令），而不使用 EXPLICIT 模式来生成层次结构。嵌套 FOR XML 查询可以生成使用 EXPLICIT 模式可生成的任何 XML。
PATH 模式与嵌套 FOR XML 查询功能一起以较简单的方式提供了 EXPLICIT 模式的灵活性。
仅当执行设置了这些模式的查询时，这些模式才有效。它们不会影响以后执行的任何查询的结果。
APPLY 运算符
APPLY是SQL SERVER 2005中新增的一个运算符，功能有点类似于不带WHERE条件的JOIN，但不尽相同，APPLY更灵活，重要的差别是APPLY中的参数可以是另一个表中的字段。
使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列
表。
APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值
函数生成的列中的值为 NULL。
分页
对于分页有很多方法可以实现，比如最常用的TOP ，MAX实现，但你会发现这些都要求表中有一个不
重复的递增字段。
在该实例中我们用ROW_NUMBER 函数实现，就没有这个要求了。下面我们来看看ROW_NUMBER 函数
的用法。
ROW_NUMBER：
返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
语法：
ROW_NUMBER ( ) OVER ( [
参数:
将FROM 子句生成的结果集划入应用了ROW_NUMBER 函数的分区。(PARTITION BY) PARTITION BY
将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。
确定将ROW_NUMBER 值分配给分区中的行的顺序。
OVER子句:
确定在应用关联的开窗函数之前，行集的分区和排序。
适用范围：排名开窗函数聚合开窗函数。
例(查询每个部门中用户的排序号(FUserSort)最小的用户信息)
select*
from(
select D.FName as DepartmentName,U.FRealName,FUsersSort,
row_number()over(partition by D.FID order by U.FUsersSort)as Row
from CY_Users U
LEFT JOIN CY_Department D ON U.FDepartmentID = D.FID
)as PagedResults
where Row >=0 and Row<=1
order by FUsersSort
扩展:
4个排名函数:
ROW_NUMBER
DENSE_RANK
RANK:
NTILE
ROW_NUMBER:
返回结果集分区内行的序列号，每个分区的第一行从 1 开始,是连续的,无并列的.
RANK:
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。(可能不连续,可能存在并列的)
但可以使用多个字段进行排序,以实现连续.
备注:
如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两位顶尖销售员具有相同的SalesYTD 值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大SalesYTD 的销售人员将排名第三。因此，RANK 函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。
DENSE_RANK:
返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加
一。(连续的, 可能存在并列的)
备注:
如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。例如，如果两位顶尖销售员具有相同的SalesYTD值，则他们将并列第一。接下来SalesYTD最高的销售人员排名第二。该排名等于该行之前的所有行数加一。因此，DENSE_RANK 函数返回的数字没有间断，
并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的
第一行。
NTILE:
将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将
返回此行所属的组的编号。(每组一个编号)
语法:
NTILE (integer_expression) OVER ( [
参数:
integer_expression
一个正整数常量表达式，用于指定每个分区必须被划分成的组数
备注:
如果分区的行数不能被integer_expression 整除，则将导致一个成员有两种大小不同的组。按照OVER 子句指定的顺序，较大的组排在较小的组前面。例如，如果总行数是53，组数是5，则前三个组每组包含11 行，其余两个组每组包含10 行。另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。例如，如果总行数为50，有五个组，则每组将包含10 行。