请问如何给Select 记录集增加一个序号列

[yiyezhiqiu]

我想在这个记录集第一列增加一个序号
select (select sum(1) from 你的表 where 你的自增字段<=a.你的自增字段) num,*
from 你的表 a
这种方法只能应付一个表不能解决两个表连接的情况 我想得到这样的记录
Select 序号列, a.1, a.2 ,b1 From a inner join b on a.1=b.1
where ... order by a.1
其中的序号列怎么写呢?

[点点星灯]

select identity(int,1,1) as '序号列', * into #t from dbo.column11

select * from #t
drop table #t

[点点星灯]

select identity(int,1,1) as '序号列', kk.* into #t from
(Select 序号列, a.1, a.2 ,b1 From a inner join b on a.1=b.1
where ... order by a.1 ) kk

select * from #t
drop table #t
--这样应该可以满足你的要求.

[yiyezhiqiu]

能用一句Select 解决吗？

[monk188]

关注！中！
我也马上也面临这个问题！！！

[wangdehao]

select (select sum(1) from(Select * From a inner join b on a.1=b.1
where ...)m where m.自增字段<t.自增字段)as num,* from (Select * From a inner join b on a.1=b.1
where ...)t order by a.1

--不用临时表的话,可以这样,不过没测试哦

[点点星灯]

--参考
declare @chengji table(xuehao varchar(20),xingming varchar(50),zongfen int)

insert @chengji(xuehao,xingming,zongfen)
values('0010','lixue',500)
insert @chengji(xuehao,xingming,zongfen)
values('0015','zhansi',456)
insert @chengji(xuehao,xingming,zongfen)
values('0014','heyao',512)
insert @chengji(xuehao,xingming,zongfen)
values('0016','woshi',423)

select * from @chengji

declare @newchengji table(mingci int,xuehao varchar(20),xingming varchar(50),zongfen int)
insert @newchengji
select 1+isnull((select count(*) from @chengji where zongfen>a.zongfen),0)
as mingci,xuehao,xingming,zongfen from @chengji a
order by zongfen desc

select * from @newchengji

[不假思索]

select count( * )....................
用计数的办法试试。

[happywang11]

可以用一条SELECT语句aw511的方法不错啊

[MorningTea]

楼主， wangdehao(找找找)的可以，就是你的select (select sum(1) from 你的表 where 你的自增字段<=a.你的自增字段) num,*
from 你的表 a
的基础上改改，加上关联

[Corporal]

收藏

[booby_fly]

学习

[Allan168]

mark