-->2005 select 自动编号=row_number() over (order by id),* from a表
--sql 2000 select * , new_id = (select count(1) from tb where id > t.id) + 1 from tb t--sql 2005 select * , new_id = row_number() over(order by id desc) from tb t
-->2000 select 自动编号=(select count(*) from a表 where id<=a.id),* from a表 a
--sql 2000 create table tb(id int, name varchar(10)) insert into tb values(5 , 'Jack') insert into tb values(4 , 'Tom') insert into tb values(3 , 'Su') goselect new_id = (select count(1) from tb where id > t.id) + 1 , * from tb tdrop table tb/* new_id id name ----------- ----------- ---------- 1 5 Jack 2 4 Tom 3 3 Su(所影响的行数为 3 行)*/
--sql 2005 create table tb(id int, name varchar(10)) insert into tb values(5 , 'Jack') insert into tb values(4 , 'Tom') insert into tb values(3 , 'Su') goselect new_id = row_number() over(order by id desc) , * from tb tdrop table tb/* new_id id name -------------------- ----------- ---------- 1 5 Jack 2 4 Tom 3 3 Su(3 行受影响) */
感谢大家及时回复 可能我举例不太恰当 id不是自动编号列,它是个价格列,不好意思,重新教我下吧~ id name ... --------------- 1 12.00 Jack 2 15.00 Tom 3 18.00 Su ......
看了duwugui的回贴才知道楼主的真正需求 create table tb(id int, name varchar(10)) insert into tb values(5 , 'Jack') insert into tb values(4 , 'Tom') insert into tb values(3 , 'Su') go --SQL2000:select 自动编号 = (select count(1) from tb where id >= t.id) , * from tb t order by id desc --SQL2005 select 自动编号=row_number() over (order by id desc),* from a表drop table tb 自动编号 id name ----------- ----------- ---------- 1 5 Jack 2 4 Tom 3 3 Su(所影响的行数为 3 行)
create table tb(id numeric(18,2), name varchar(10)) insert into tb values(12 , 'Jack') insert into tb values(15 , 'Tom') insert into tb values(18 , 'Su') goselect 自动编号 = (select count(1) from tb where id >= t.id) , * from tb t order by id descdrop table tb 自动编号 id name ----------- -------------------- ---------- 1 18.00 Su 2 15.00 Tom 3 12.00 Jack(所影响的行数为 3 行)一样的
create table tb(id numeric(18,2), name varchar(10)) insert into tb values(12 , 'Jack') insert into tb values(15 , 'Tom') insert into tb values(18 , 'Su') goselect 自动编号 = (select count(1) from tb where id <= t.id) , * from tb t order by iddrop table tb自动编号 id name ----------- -------------------- ---------- 1 12.00 Jack 2 15.00 Tom 3 18.00 Su(所影响的行数为 3 行)要正过来就这样
-->2005
select
自动编号=row_number() over (order by id),*
from a表
select * , new_id = (select count(1) from tb where id > t.id) + 1 from tb t--sql 2005
select * , new_id = row_number() over(order by id desc) from tb t
select
自动编号=(select count(*) from a表 where id<=a.id),*
from a表 a
create table tb(id int, name varchar(10))
insert into tb values(5 , 'Jack')
insert into tb values(4 , 'Tom')
insert into tb values(3 , 'Su')
goselect new_id = (select count(1) from tb where id > t.id) + 1 , * from tb tdrop table tb/*
new_id id name
----------- ----------- ----------
1 5 Jack
2 4 Tom
3 3 Su(所影响的行数为 3 行)*/
--sql 2005
create table tb(id int, name varchar(10))
insert into tb values(5 , 'Jack')
insert into tb values(4 , 'Tom')
insert into tb values(3 , 'Su')
goselect new_id = row_number() over(order by id desc) , * from tb tdrop table tb/*
new_id id name
-------------------- ----------- ----------
1 5 Jack
2 4 Tom
3 3 Su(3 行受影响)
*/
可能我举例不太恰当
id不是自动编号列,它是个价格列,不好意思,重新教我下吧~ id name ...
---------------
1 12.00 Jack
2 15.00 Tom
3 18.00 Su
......
create table tb(id int, name varchar(10))
insert into tb values(5 , 'Jack')
insert into tb values(4 , 'Tom')
insert into tb values(3 , 'Su')
go
--SQL2000:select 自动编号 = (select count(1) from tb where id >= t.id) , * from tb t order by id desc
--SQL2005
select
自动编号=row_number() over (order by id desc),*
from a表drop table tb
自动编号 id name
----------- ----------- ----------
1 5 Jack
2 4 Tom
3 3 Su(所影响的行数为 3 行)
insert into tb values(12 , 'Jack')
insert into tb values(15 , 'Tom')
insert into tb values(18 , 'Su')
goselect 自动编号 = (select count(1) from tb where id >= t.id) , * from tb t order by id descdrop table tb
自动编号 id name
----------- -------------------- ----------
1 18.00 Su
2 15.00 Tom
3 12.00 Jack(所影响的行数为 3 行)一样的
insert into tb values(12 , 'Jack')
insert into tb values(15 , 'Tom')
insert into tb values(18 , 'Su')
goselect 自动编号 = (select count(1) from tb where id <= t.id) , * from tb t order by iddrop table tb自动编号 id name
----------- -------------------- ----------
1 12.00 Jack
2 15.00 Tom
3 18.00 Su(所影响的行数为 3 行)要正过来就这样
为什么这么写? 能翻译一下吗?