row_number( ) 先来点数据,先建个表 SET NOCOUNT ON CREATE TABLE Person( FirstName VARCHAR(10), Age INT, Gender CHAR(1)) INSERT INTO Person VALUES ('Ted',23,'M') INSERT INTO Person VALUES ('John',40,'M') INSERT INTO Person VALUES ('George',6,'M') INSERT INTO Person VALUES ('Mary',11,'F') INSERT INTO Person VALUES ('Sam',17,'M') INSERT INTO Person VALUES ('Doris',6,'F') INSERT INTO Person VALUES ('Frank',38,'M') INSERT INTO Person VALUES ('Larry',5,'M') INSERT INTO Person VALUES ('Sue',29,'F') INSERT INTO Person VALUES ('Sherry',11,'F') INSERT INTO Person VALUES ('Marty',23,'F') 直接用例子说明问题: SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age], FirstName, Age FROM Person
create table tb(name varchar(10),sex varchar(10)) insert into tb select 'aa','男' insert into tb select 'bb','女' go--增加列 alter table tb add xh int identity(1,1) go--查看 select * from tb
2005 row_number() 2000 identity
ALTER TABLE doc_exe ADD column_b INT IDENTITY(1,1)
--win2000這樣declare @Person TABLE(FirstName VARCHAR(10),Age INT,Gender CHAR(1))INSERT INTO @Person VALUES ('Ted',23,'M') INSERT INTO @Person VALUES ('John',40,'M') INSERT INTO @Person VALUES ('George',6,'M') INSERT INTO @Person VALUES ('Mary',11,'F') INSERT INTO @Person VALUES ('Sam',17,'M') INSERT INTO @Person VALUES ('Doris',6,'F') INSERT INTO @Person VALUES ('Frank',38,'M') INSERT INTO @Person VALUES ('Larry',5,'M') INSERT INTO @Person VALUES ('Sue',29,'F') INSERT INTO @Person VALUES ('Sherry',11,'F') INSERT INTO @Person VALUES ('Marty',23,'F')SELECT identity(int,1,1) as id , FirstName, Age into #A FROM @Person order by Ageselect * from #A
declare @Person TABLE(FirstName VARCHAR(10),Age INT,Gender CHAR(1))INSERT INTO @Person VALUES ('Ted',23,'M') INSERT INTO @Person VALUES ('John',40,'M') INSERT INTO @Person VALUES ('George',6,'M') INSERT INTO @Person VALUES ('Mary',11,'F') INSERT INTO @Person VALUES ('Sam',17,'M') INSERT INTO @Person VALUES ('Doris',6,'F') INSERT INTO @Person VALUES ('Frank',38,'M') INSERT INTO @Person VALUES ('Larry',5,'M') INSERT INTO @Person VALUES ('Sue',29,'F') INSERT INTO @Person VALUES ('Sherry',11,'F') INSERT INTO @Person VALUES ('Marty',23,'F')SELECT *,(SELECT COUNT(DISTINCT Age ) FROM @Person WHERE Age<=T.Age )NUM FROM @Person T ORDER BY 4(所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)FirstName Age Gender NUM ---------- ----------- ------ ----------- Larry 5 M 1 Doris 6 F 2 George 6 M 2 Mary 11 F 3 Sherry 11 F 3 Sam 17 M 4 Ted 23 M 5 Marty 23 F 5 Sue 29 F 6 Frank 38 M 7 John 40 M 8(所影响的行数为 11 行)
謝謝幫忙, 可能是我的表達不清。我已經有一個表的數據,但是,我想在表內數據中加入一組順序的號碼如下ID No. Name ====== ======= ab ac ad我想在ID No. 中加入1,2,3.....至每一條紀錄。
row_number() over(order by 字段名 desc/asc)
因為現在表內的ID No. 是沒有數值的,我想update 表內的ID No. ,而ID NO是一個INT,ID NO Name ===== ===== 1 ab 2 ac 3 ad : : : :
--一种是在创建表的时候创建自增列 create table # ( ID int identity(1,1),--自增列,从1开始,每次增1 Name nvarchar(4) ) go--插入数据 insert into # select '楼主'; insert into # select '威武'; insert into # select '无敌'; insert into # select '超神'; --一种是在select出来的结果中增加一列自增列select identity(int,1,1) as ID, Name into #temp from # goselect * from #temp;
--一种是在创建表的时候创建自增列 create table # ( ID int identity(1,1),--自增列,从1开始,每次增1 Name nvarchar(4) ) go--插入数据 insert into # select '楼主'; insert into # select '威武'; insert into # select '无敌'; insert into # select '超神'; --一种是在select出来的结果中增加一列自增列select identity(int,1,1) as ID, Name into #temp from # goselect * from #temp;--还有一种是表已存在,增加一列自增列 alter table your_table add ID identity(int,1,1)
但是我想要把這個號碼update入表內
if object_id('tb') is not null drop table tb create table tb (id int ,name varchar(10)) go insert into tb select null,'aa' union all select null,'bb' union all select null,'cc' union all select null,'dd' --update ;with t as ( select row_number() over(order by getdate()) as row,id,name from tb ) update t set id =row select * from tb /* id name ----------- ---------- 1 aa 2 bb 3 cc 4 dd(4 行受影响) */
在MS SQL 中不能使用row_number(),我的版本是8.0,是不是不能用這功能。 'row_number' is not a recognized function name.
SQLServer2000不支持row_number() 要用identity--测试表 create table _testA (badge varchar(10) ,ename varchar(10))insert _testA select 'TS0001','test01' union select 'TS0002','test01' union select 'TS0003','test01' union select 'TS0004','test01' union select 'TS0005','test01'--SQL2000中在select中用identity一定要into select identity(int,1,1) row,* into _testB from _testAselect * from _testB
Declare @id int set @id=0 --初始值为1的情况下设置为0 Update Table_Pqs Set [ID_No]=@id,@id=@id+1
这个SQL 2000,SQL 2K5,SQL 2K8都可以用
SQL 2005 ROW_NUMBER() OVER(ORDER BY COLUMNNAME) SQL 2000 IDENTITY(INT,1,1)
row_number( )
先来点数据,先建个表
SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')
直接用例子说明问题:
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
FirstName,
Age
FROM Person
create table tb(name varchar(10),sex varchar(10))
insert into tb select 'aa','男'
insert into tb select 'bb','女'
go--增加列
alter table tb add xh int identity(1,1)
go--查看
select * from tb
row_number()
2000
identity
INSERT INTO @Person VALUES ('John',40,'M')
INSERT INTO @Person VALUES ('George',6,'M')
INSERT INTO @Person VALUES ('Mary',11,'F')
INSERT INTO @Person VALUES ('Sam',17,'M')
INSERT INTO @Person VALUES ('Doris',6,'F')
INSERT INTO @Person VALUES ('Frank',38,'M')
INSERT INTO @Person VALUES ('Larry',5,'M')
INSERT INTO @Person VALUES ('Sue',29,'F')
INSERT INTO @Person VALUES ('Sherry',11,'F')
INSERT INTO @Person VALUES ('Marty',23,'F')SELECT identity(int,1,1) as id ,
FirstName,
Age into #A
FROM @Person order by Ageselect * from #A
INSERT INTO @Person VALUES ('John',40,'M')
INSERT INTO @Person VALUES ('George',6,'M')
INSERT INTO @Person VALUES ('Mary',11,'F')
INSERT INTO @Person VALUES ('Sam',17,'M')
INSERT INTO @Person VALUES ('Doris',6,'F')
INSERT INTO @Person VALUES ('Frank',38,'M')
INSERT INTO @Person VALUES ('Larry',5,'M')
INSERT INTO @Person VALUES ('Sue',29,'F')
INSERT INTO @Person VALUES ('Sherry',11,'F')
INSERT INTO @Person VALUES ('Marty',23,'F')SELECT *,(SELECT COUNT(DISTINCT Age ) FROM @Person WHERE Age<=T.Age )NUM FROM @Person T ORDER BY 4(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)FirstName Age Gender NUM
---------- ----------- ------ -----------
Larry 5 M 1
Doris 6 F 2
George 6 M 2
Mary 11 F 3
Sherry 11 F 3
Sam 17 M 4
Ted 23 M 5
Marty 23 F 5
Sue 29 F 6
Frank 38 M 7
John 40 M 8(所影响的行数为 11 行)
====== =======
ab
ac
ad我想在ID No. 中加入1,2,3.....至每一條紀錄。
===== =====
1 ab
2 ac
3 ad
: :
: :
--一种是在创建表的时候创建自增列
create table # (
ID int identity(1,1),--自增列,从1开始,每次增1
Name nvarchar(4)
)
go--插入数据
insert into # select '楼主';
insert into # select '威武';
insert into # select '无敌';
insert into # select '超神';
--一种是在select出来的结果中增加一列自增列select identity(int,1,1) as ID, Name
into #temp
from #
goselect * from #temp;
--一种是在创建表的时候创建自增列
create table # (
ID int identity(1,1),--自增列,从1开始,每次增1
Name nvarchar(4)
)
go--插入数据
insert into # select '楼主';
insert into # select '威武';
insert into # select '无敌';
insert into # select '超神';
--一种是在select出来的结果中增加一列自增列select identity(int,1,1) as ID, Name
into #temp
from #
goselect * from #temp;--还有一种是表已存在,增加一列自增列
alter table your_table add ID identity(int,1,1)
create table tb (id int ,name varchar(10))
go
insert into tb select null,'aa'
union all select null,'bb'
union all select null,'cc'
union all select null,'dd'
--update
;with t as
(
select row_number() over(order by getdate()) as row,id,name from tb
)
update t set id =row select * from tb
/*
id name
----------- ----------
1 aa
2 bb
3 cc
4 dd(4 行受影响)
*/
'row_number' is not a recognized function name.
create table _testA
(badge varchar(10)
,ename varchar(10))insert _testA
select 'TS0001','test01'
union
select 'TS0002','test01'
union
select 'TS0003','test01'
union
select 'TS0004','test01'
union
select 'TS0005','test01'--SQL2000中在select中用identity一定要into
select identity(int,1,1) row,*
into _testB
from _testAselect * from _testB
Declare @id int
set @id=0 --初始值为1的情况下设置为0
Update Table_Pqs Set [ID_No]=@id,@id=@id+1
ROW_NUMBER() OVER(ORDER BY COLUMNNAME)
SQL 2000
IDENTITY(INT,1,1)