select id,[name],age ,row_number=(select count(*) from tablename b where a.id<=b.id hwere )from TableName a
identity(int,1,1)怎么用啊,大哥。。
还是表自联生成编号准确,如果用自增列的话,容易产生编号缺失。 select id,[name],age ,row_number=(select count(1) + 1 from TableName b where a.id < b.id hwere ) from TableName a
-->******************************************************* -->Microsoft SQL Server Management Studio Complete 2008*** -->AUTHOR : Mr wang ********** -->CREATE TIME : 2010-11-22 08:40:52 ************** -->******************************************************* --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB]---->建表 create table [TB]([id] int,[name] varchar(2),[age] int) insert [TB] select 1,'aa',22 union all select 2,'bb',33 union all select 3,'cc',44 union all select 4,'dd',55--> 查询结果 SELECT * ,rw=(select COUNT(1) from TB where id<=t.id) FROM [TB] t--> 删除表格 DROP TABLE [TB]
select id,[name],age , row_number=(select count(*)+1 from tb where t.id<=id) from tb tselect id,[name],age , row_number=row_number()over(partition by id order by id) from tb t
if object_id('[tb1]') is not null drop table [tb1] create table [tb1]([id] int,[name] varchar(4),[age] int,) insert [tb1] select 1,'张三',18 union all select 2,'李四',18 union all select 3,'王五',20 union all select 4,'王中',19 drop table tb1 select IDENTITY(INT,1,1) as rownumber,* into #A from tb1
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB]---->建表 create table [TB]([id] int,[name] varchar(2),[age] int) insert [TB] select 1,'aa',22 union all select 2,'bb',33 union all select 3,'cc',44 union all select 4,'dd',55select id,[name],age , row_number=(select count(*) from tb where id<=t.id) from tb tselect id,[name],age , row_number=row_number()over(partition by id order by id) from tb t
select identity(int,1,1) as row_number,* into #aa from 表名 select * from #aa drop table #aa 用标识列, 生成行号效果!!
select id,[name],age ,row_number=(select count(1) + 1 from TableName b where a.id < b.id hwere )
from TableName a
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-22 08:40:52 **************
-->*******************************************************
--> 测试数据:[TB]
if object_id('[TB]') is not null
drop table [TB]---->建表
create table [TB]([id] int,[name] varchar(2),[age] int)
insert [TB]
select 1,'aa',22 union all
select 2,'bb',33 union all
select 3,'cc',44 union all
select 4,'dd',55--> 查询结果
SELECT * ,rw=(select COUNT(1) from TB where id<=t.id)
FROM [TB] t--> 删除表格
DROP TABLE [TB]
select id,[name],age ,
row_number=(select count(*)+1 from tb where t.id<=id)
from tb tselect id,[name],age ,
row_number=row_number()over(partition by id order by id)
from tb t
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([id] int,[name] varchar(4),[age] int,)
insert [tb1]
select 1,'张三',18 union all
select 2,'李四',18 union all
select 3,'王五',20 union all
select 4,'王中',19
drop table tb1
select IDENTITY(INT,1,1) as rownumber,* into #A from tb1
--> 测试数据:[TB]
if object_id('[TB]') is not null
drop table [TB]---->建表
create table [TB]([id] int,[name] varchar(2),[age] int)
insert [TB]
select 1,'aa',22 union all
select 2,'bb',33 union all
select 3,'cc',44 union all
select 4,'dd',55select id,[name],age ,
row_number=(select count(*) from tb where id<=t.id)
from tb tselect id,[name],age ,
row_number=row_number()over(partition by id order by id)
from tb t
select * from #aa
drop table #aa
用标识列, 生成行号效果!!