获取带序号的查询结果:
假设有Customer表,该表有CustomerID,CustomerName等列,其中CustomerID为Identity列,假设现在需要对Customer表做一项排名,列出前十名的Customer,并在查询结果中带上名次
思路:建立一个procedure,在procedure中建立一个带Identity列的临时表,将数据插入临时表,再从临时表中检索数据
Create Procedure IdentityTableTest
AS
BEGIN
create Table #TableIdentityTest
(
CustomerID int Identity(1,1),
CustomerName varchar(50)
)
insert #TableIdentityTest select top 10 CustomerName from Customer order by ****
select * from #TableIdentityTest
END
执行存储过程IdentityTableTest即可获得排序结果