问题 关于sql查询
我要查询数据库表里mm表，查询年龄大于50，我想在查询的结果里加一个序号，如1，2，3...，
如，select name from mm where nl>50
有什么加一个序号，而且序号是顺序的
结果形式是：
1 bcy
2 ldy
3 ml
. .
. .
. .
. .
. .
问题点数：0、回复次数：9Top
1 楼realgz（realgz）回复于 2003-12-02 16:58:56 得分 0
目前主要有2种方法
1,select * into #tmp from table
alter #tmp add cnt identity(1,1)
select * from #tmp
2,有一个可以唯一区分的列或列组合
select *,(select count(*) from table t2 where t2.id<=t.id) as cnt from table tTop
2 楼wzh1215（懒猫）回复于 2003-12-02 16:59:10 得分 0
如果表里有主键：
select (select count(*)+1 from mm a where a.主键>主键 and n1>50) as id,name from mm where n1>50Top
3 楼zarge（鲨去来兮）回复于 2003-12-02 16:59:11 得分 0
select identity(int, 1, 1) as sn, name into #tmp from mm where nl>50
select * from #tmpTop
4 楼realgz（realgz）回复于 2003-12-02 16:59:39 得分 0
错了
1,select * into #tmp from table
alter #tmp add cnt int identity(1,1)
select * from #tmp
2,要有一个可以唯一区分的列或列组合
select *,(select count(*) from table t2 where t2.id<=t.id) as cnt from table t
Top
5 楼sdhdy（大江东去...）回复于 2003-12-02 17:00:04 得分 0
select identity(int,1,1) FID,name into #temp from from mm where nl>50
select * from #temp
drop table #tempTop
6 楼lansquenet（佣兵小队长）回复于 2003-12-02 17:00:32 得分 0
SELECT identity(int, 1, 1) id, name INTO # FROM mm WHERE nl>50
SELECT * FROM #
Top
7 楼sdhdy（大江东去...）回复于 2003-12-02 17:00:33 得分 0
select identity(int,1,1) FID,name into #temp from mm where nl>50
select * from #temp
drop table #temp
Top
8 楼victorycyz（--）回复于 2003-12-02 17:03:21 得分 0
select a.name into #A from MM a where a.nl>50
alter table #A add id int identity(1,1)
select * from #A
Top
9 楼xgli2002（）回复于 2003-12-02 17:16:50 得分 0
create table #tem(no int identity,name varchar(20))
insert #tem select name from mm where n1>50
select * from #tem
drop table #tem
Top
