如何在查询的时候添加一个序号列？

Question

select * from APTEST
结果如下：
--------------------------------------------------------------------
BranchCD LineCD GroupCD ProductCD QtyCount MoneyCount
32  1 1 1001         100.0000 100.0000
32  1 1 1002         100.0000 100.0000
32  1 2 1003         100.0000 100.0000
32  1 2 1004         100.0000 100.0000
32  2 1 1005         100.0000 100.0000
32  2 1 1006         100.0000 100.0000
64  1 1 1007         100.0000 100.0000
--------------------------------------------------------------------
想要这种结果，在查询的时候添加一个序号列，我的操作：
SELECT 
identity(int , 1,1) AS N
,*
INTO #A
FROM APTEST
SELECT * FROM #A
想问下如果不用临时表能不能实现呢
----------------------------------------------------------------------------------
N BranchCD LineCD GroupCD ProductCD QtyCount MoneyCount
1 32           1    1     1001 100.0000 100.0000
2 32           1    1     1002 100.0000 100.0000
3 32           1    2     1003 100.0000 100.0000
4 32           1    2     1004 100.0000 100.0000
5 32           2    1     1005 100.0000 100.0000
6 32           2    1     1006 100.0000 100.0000
7 64           1    1     1007 100.0000 100.0000

Answer 1

--> Test Data: @T
declare @T table ([BranchCD] int,[LineCD] int,[GroupCD] int,[ProductCD] int,[QtyCount] numeric(7,4),[MoneyCount] numeric(7,4))
insert into @T
select 32,1,1,1001,100.0000,100.0000 union all
select 32,1,1,1002,100.0000,100.0000 union all
select 32,1,2,1003,100.0000,100.0000 union all
select 32,1,2,1004,100.0000,100.0000 union all
select 32,2,1,1005,100.0000,100.0000 union all
select 32,2,1,1006,100.0000,100.0000 union all
select 64,1,1,1007,100.0000,100.0000--select * from @T
--Code
--SQL2000
select N=(select count(*)+1 from @T where [ProductCD]<a.[ProductCD]),
* from @T a--SQL2005
select N=ROW_NUMBER() over(order by [ProductCD]),
* from @T a--Result
/*
N           BranchCD    LineCD      GroupCD     ProductCD   QtyCount                                MoneyCount
----------- ----------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
1           32          1           1           1001        100.0000                                100.0000
2           32          1           1           1002        100.0000                                100.0000
3           32          1           2           1003        100.0000                                100.0000
4           32          1           2           1004        100.0000                                100.0000
5           32          2           1           1005        100.0000                                100.0000
6           32          2           1           1006        100.0000                                100.0000
7           64          1           1           1007        100.0000                                100.0000
*/