日期:2014-05-16  浏览次数:20541 次

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

插入测试数据:

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