sql ²éѯ±íµÄÁÐÃû row_number()

select upper(name) from syscolumns
where id =any
(
select Id from sysobjects where name='tb_city'
)
lower
select row_number() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É  ×ÔÔö  123
select rank() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É×ÔÔö123  ¸ù¾ÝÅÅÐòµÄ×ֶΠ Öظ´µÄ ²»×ÔÔö  Èô 3Ìõ¼Ç¼µÄÁ½¸ö×Ö¶ÎÊÇÖظ´µÄ  Ôò ½á¹ûÊÇ  1 1 3
select DENSE_RANK() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É×ÔÔö123  ¸ù¾ÝÅÅÐòµÄ×ֶΠ Öظ´µÄ ²»×ÔÔö  Èô 3Ìõ¼Ç¼µÄÁ½¸ö×Ö¶ÎÊÇÖظ´µÄ  Ôò ½á¹ûÊÇ  1 1 2
select NTILE(num) over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É×ÔÔö123  ¸ù¾ÝÅÅÐòµÄ×ֶΠ Öظ´µÄ  ²»×ÔÔö   ÇÒ×ÔÔöµÄÐòÁРСÓÚnum  Èô 4Ìõ¼Ç¼µÄÁ½¸ö×Ö¶ÎÊÇÖظ´µÄ  ÇÒnum=2  Ôò ½á¹ûÊÇ  1 1 22