MS SQL2005中ROW_NUMBER() OVER函数的用法
项目中sql语句源码：
select top 5 *, ROW_NUMBER() OVER( order by LastUpdTime desc,id) as RowNumber from (select LastUpdTime'C'+convert(varchar(5),c.c_id) as id, shop.Shop_ID,shop.Shop_TEL,f.f_no,shop.Shop_storeys,shop.shop_mng,c.Pledge,c.LivingRoom,c.KitchenRoom,c.Bathroom,shop.title,shop.xiaoqu,shop.Shop_Name,c.LastUpdTime,shop.city,isnull(shop.area,'')+isnull(shop.domtom,'')as addr,c.Room_count,c.c_size,c.c_small,c.C_MODE,c.fitment,c.CToward,shop.elevator,c.Source_,c.c_type,c.rent,c.C_Price,c.Config,c.C_memo,(select top 1 p.Addr from Shop_Union_Photo p where shop.Shop_ID=p.Parent_id)as 'ImgAddr',shop.XPOS_ID,''as woshi from Shop_Union shop,Shop_Union_Floor F,Shop_Union_Class c where shop.Shop_ID=F.Shop_ID and F.F_ID=c.F_ID and c.C_ID not in(select C_ID from Shop_Union_Room) and c.Shelves=1 and c_type in('1','2','3') union all select 'R'+convert(varchar(5),r.r_id) as id, shop.Shop_ID,shop.Shop_TEL,f.f_no,shop.Shop_storeys,shop.shop_mng,c.Pledge,c.LivingRoom,c.KitchenRoom,c.Bathroom,shop.title,shop.xiaoqu,shop.Shop_Name,c.LastUpdTime,shop.city,isnull(shop.area,'')+isnull(shop.domtom,'')as addr,c.Room_count,c.c_size,c.c_small,c.C_MODE,c.fitment,c.CToward,shop.elevator,c.Source_,c.c_type,c.rent,c.C_Price,c.Config,c.C_memo,(select top 1 p.Addr from Shop_Union_Photo p where shop.Shop_ID=p.Parent_id)as 'ImgAddr',shop.XPOS_ID,r.woshi from Shop_Union shop,Shop_Union_Floor F,Shop_Union_Class c,Shop_Union_Room r where shop.Shop_ID=F.Shop_ID and F.F_ID=c.F_ID and c.C_ID=r.C_ID and c.Shelves=1 and c_type in('1','2','3'))as a where 1=1 and a.city='深圳市' order by newid()
select top 20 *, ROW_NUMBER() OVER( order by LastUpdTime desc,id)----这是神马情况？？？？
看了网上的资料和同事说的，我恍然大悟。
引用资料上的一句话：row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。
OK，就这么简单，为查询结果生成一个序号而已。
在使用row_number函数是要使用over子句选择对某一列进行排序，然后才能生成序号。
原SQL语句：select row_number() over(order by field2 desc) as row_number,* from t_table order by field1 desc
等等。原sql语句中over子句是order by field2 desc，但是，我项目中的over子句是order by LastUpdTime desc,id，“,id”是神马意思？？？
我同事刚开始跟我说就是先根据更新时间降序排列，再根据id升序排列。我当时就晕了，又降又升的，这不乱套了？
后来自己新建了一个数据库测试row_number()，才明白是怎么回事。
原来，是先根据更新时间降序排列，如果更新时间相同的话再根据id升序排列！
Fuc_！有种被坑的感觉。
资料参考与：1.http://www.cnblogs.com/icebutterfly/archive/2009/08/05/1539657.html
