附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第二节Work with Data 数据处理 。

第一部分：Query data by using SELECT statements. May include but not limited to: use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce. 用SELECT语句查询数据。可能包含但不仅限于：在一个查询中使用排名函数(ranking function)进行前（top）X行的多类数据筛选；使用SQL 2005或2008或更高版本的SQL关键字创建并执行高效的查询，比如 synonym, join (except, intersect)等关键字；使用动态SQL和系统元数据进行逻辑实现；编写高效的技术复杂的SQL查询，包括各种表联接的使用；根据已提供的表判断给出的SQL语句是否能正确执行；根据提供的带约束的表判断SQL语句是否可以加载表；使用并理解不同的数据访问技术；对比CASE, ISNULL, COALESCE;

• 在一个查询中使用排名函数(ranking function)进行前（top）X行的多类数据筛选use the ranking function to select top(X) rows for multiple categories in a single query.。

四个ranking function: RANK，DENSE_RANK，NTILE，ROW_NUMBER：

创建测试表：

create table test(
id int identity(1,1) primary key,
testid int,
name varchar(100)
)

插入测试数据：

insert into test(testid,name)select 1,'乐可乐可的部落格'
insert into test(testid,name)select 1,'乐可乐可的部落格'
insert into test(testid,name)select 1,'乐可乐可的部落格'
insert into test(testid,name)select 2,'乐可乐可的部落格'
insert into test(testid,name)select 3,'微软考试认证'
insert into test(testid,name)select 4,'微软考试认证'
insert into test(testid,name)select 4,'微软考试认证'

用一个SQL语句来查看各个函数的使用：

select id,testid,
ROW_NUMBER() over( order by testid) as rownum,
RANK() over(order by testid) as ranknum,
DENSE_RANK() over(order by testid) as denseranknum,
Ntile(4) over ( order by testid) as ntilenum
from test
order by testid

下面是运行结果：

id    testid    rownum    ranknum    denseranknum    ntilenum
1    1            1                 1                   1                             1
2    1            2                 1   &n