select c1,c2,row_number() over (order by ids) as ids from tb
if object_id('test') is not null drop table test create table test ( id int primary key )insert into test select 1 union all select 3 union all select 5 union all select 8 union all select 10 union all select 11 select *,(select count(1) from test where id <= t.id) from test t/** id ----------- ----------- 1 1 3 2 5 3 8 4 10 5 11 6(所影响的行数为 6 行) **/
Create table #a ( a int,b datetime,c int) insert into #a select 1 , '2009-06-01' , 1 union all select 2 , '2009-06-01' , 2 union all---------------------要过滤掉的数据 select 2 , '2009-06-02' , 3 union all select 2 , '2009-07-01' , 3 union all select 3 , '2009-06-01' , 2 select *,row_number() over (order by a) '行数' from #a
row_number() 需要2005及以上版本支持。
select * from (select *,row_number() over (order by a) '行数' from #a) as b where b='2009-07-01'-------------结果--------------- a b c 行数 2 2009-07-01 00:00:00.000 3 4
还是不懂,用row_number() over (order by a) 和三楼的select count(1) 均有错,怎么回事???
假设表a 中有两列数据 name 和 age 其中 name age zhang 16 li 17 meng 19 zhu 20 要求查询结果是 xh name age 1 zhang 16 2 li 17 3 meng 19 4 zhu 20
你自己没有ID字段? 如果没有ID字段,则可以增加一个自增字段.
我是随便写的一个表,用row_number() 在SQL2005种提示是不可识别的函数名
declare @a table ( id int ,shop varchar(10) ,[desc] varchar(50) ,ndqty int ); insert into @a values (1,'a','aa',10) ,(2,'b','bb',12) ,(3,'c','cc',15) ,(4,'a','aa',20);select ROW_NUMBER() over (order by id desc) as rowid ,* from @a
if object_id('test') is not null drop table test create table test ( id int primary key )insert into test select 1 union all select 3 union all select 5 union all select 8 union all select 10 union all select 11 select *,IDENTity(int,1,1) as xh into ## from test select * from ##(6 行受影响) id xh ----------- ----------- 1 1 3 2 5 3 8 4 10 5 11 6
if object_id('tb') is not null drop table tb create table tb ( id int primary key )insert into tb select 1 union all select 3 union all select 5 union all select 8 union all select 10 union all select 11 select id,row_number() over(order by id) as rownum from tb(6 行受影响) id rownum -------------- 1 1 3 2 5 3 8 4 10 5 11 6
基于SQL2005前使用如下 select id,(select count(*) from tb as S2 where S2.id<=S1.id)as rownum from tb as S1 order by id id rownum -------------- 1 1 3 2 5 3 8 4 10 5 11 6
--2005的方法 SELECT ROW_NUMBER() OVER (ORDER BY [VALUE]) AS 序号,ID,[VALUE] FROM t --2000的方法 if object_id('##') is not null drop table ## select 序号=identity(1,1),[ID],[VALUE] into ## from tb select * from ##
if object_id('test') is not null drop table test create table test ( id int primary key )insert into test select 1 union all select 3 union all select 5 union all select 8 union all select 10 union all select 11 select *,(select count(1) from test where id <= t.id) from test t/** id ----------- ----------- 1 1 3 2 5 3 8 4 10 5 11 6(所影响的行数为 6 行) **/
select *,row_number() over (order by getdate()) as '行数' from #a
select *,rank() over (order by getdate()) as '行数' from #a rank()函数也是可以的。
sqlserver2000declare @tbx table (id int IDENTITY(1,1),c1 varchar(30),c2 varchar(50)) insert into @tbx (c1,c2) select c1,c2 from tb1 select * from @tbx
select *,row_number() over (order by getdate()) as '行数' from #a
为什么我的2005 使用row_number() 就提示是不可识别的函数名
if object_id('test') is not null drop table test create table test ( name char(20),age int )insert into test select 'zhang',16 union all select 'li',17 union all select 'meng',19 union all select 'zhu',20 select (select count(1) from test where age<=a.age)xh,* from test axh name age 1 zhang 16 2 li 17 3 meng 19 4 zhu 20 4楼方法很巧妙 但是有局限性
if object_id('test') is not null
drop table test
create table test
(
id int primary key
)insert into test
select 1 union all
select 3 union all
select 5 union all
select 8 union all
select 10 union all
select 11 select *,(select count(1) from test where id <= t.id) from test t/**
id
----------- -----------
1 1
3 2
5 3
8 4
10 5
11 6(所影响的行数为 6 行)
**/
Create table #a ( a int,b datetime,c int)
insert into #a
select 1 , '2009-06-01' , 1 union all
select 2 , '2009-06-01' , 2 union all---------------------要过滤掉的数据
select 2 , '2009-06-02' , 3 union all
select 2 , '2009-07-01' , 3 union all
select 3 , '2009-06-01' , 2 select *,row_number() over (order by a) '行数' from #a
需要2005及以上版本支持。
select * from (select *,row_number() over (order by a) '行数' from #a) as b
where b='2009-07-01'-------------结果---------------
a b c 行数
2 2009-07-01 00:00:00.000 3 4
其中 name age
zhang 16
li 17
meng 19
zhu 20
要求查询结果是
xh name age
1 zhang 16
2 li 17
3 meng 19
4 zhu 20
如果没有ID字段,则可以增加一个自增字段.
(
id int
,shop varchar(10)
,[desc] varchar(50)
,ndqty int
);
insert into @a
values (1,'a','aa',10)
,(2,'b','bb',12)
,(3,'c','cc',15)
,(4,'a','aa',20);select ROW_NUMBER() over (order by id desc) as rowid
,*
from @a
drop table test
create table test
(
id int primary key
)insert into test
select 1 union all
select 3 union all
select 5 union all
select 8 union all
select 10 union all
select 11 select *,IDENTity(int,1,1) as xh into ## from test
select * from ##(6 行受影响)
id xh
----------- -----------
1 1
3 2
5 3
8 4
10 5
11 6
if object_id('tb') is not null
drop table tb
create table tb
(
id int primary key
)insert into tb
select 1 union all
select 3 union all
select 5 union all
select 8 union all
select 10 union all
select 11
select id,row_number() over(order by id) as rownum from tb(6 行受影响)
id rownum
--------------
1 1
3 2
5 3
8 4
10 5
11 6
select id,(select count(*) from tb as S2 where S2.id<=S1.id)as
rownum from tb as S1 order by id
id rownum
--------------
1 1
3 2
5 3
8 4
10 5
11 6
SELECT ROW_NUMBER() OVER (ORDER BY [VALUE]) AS 序号,ID,[VALUE] FROM t
--2000的方法
if object_id('##') is not null
drop table ##
select 序号=identity(1,1),[ID],[VALUE] into ## from tb
select * from ##
drop table test
create table test
(
id int primary key
)insert into test
select 1 union all
select 3 union all
select 5 union all
select 8 union all
select 10 union all
select 11 select *,(select count(1) from test where id <= t.id) from test t/**
id
----------- -----------
1 1
3 2
5 3
8 4
10 5
11 6(所影响的行数为 6 行)
**/
select *,row_number() over (order by getdate()) as '行数' from #a
rank()函数也是可以的。
insert into @tbx (c1,c2) select c1,c2 from tb1
select * from @tbx
if object_id('test') is not null
drop table test
create table test
(
name char(20),age int
)insert into test
select 'zhang',16
union all
select 'li',17
union all
select 'meng',19
union all
select 'zhu',20
select (select count(1) from test where age<=a.age)xh,* from test axh name age
1 zhang 16
2 li 17
3 meng 19
4 zhu 20 4楼方法很巧妙 但是有局限性