微软认证考试70-461 Work with Data 数据处理 --27%比重--(1)

附注：微软认证考试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)
)

插入测试数据