查询时加序号:
a:没有主键的情形:
Select identity(int,1,1) as iid,* into #tmp from TableName
Select * from #tmp
Drop table #tmp
b:有主键的情形:
Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a
a:没有主键的情形:
Select identity(int,1,1) as iid,* into #tmp from TableName
Select * from #tmp
Drop table #tmp
b:有主键的情形:
Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a
本例:
1:如果没有主键
Select identity(int,1,1) as iid,* into #tmp from stu
Select * from #tmp
Drop table #tmp
2: 如果s_name为主键
select (select sum(1) from stu where s_name<=a.s_name) as iid,* from stu a
from stu a order by s_name
declare @STU table(s_no varchar(4),s_name char(2))
insert into @stu
select '0071','aa'
union all select '0045','bb'
union all select '0053','cc'--如果s_no为主键
select 序号=(select sum(1) from @stu where s_no<=a.s_no),*
from @stu a order by s_no--如果s_name为主键
select 序号=(select sum(1) from @stu where s_name<=a.s_name),*
from @stu a order by s_name/*--测试结果序号 s_no s_name
----------- ---- ------
1 0045 bb
2 0053 cc
3 0071 aa(所影响的行数为 3 行)序号 s_no s_name
----------- ---- ------
1 0071 aa
2 0045 bb
3 0053 cc(所影响的行数为 3 行)
--*/