6,129
社区成员
发帖
与我相关
我的任务
分享
--查询
--sql 2000
select 图二 = (select count(1) from tb where 排序 = t.排序 and 图一 < t.图一) + 1 , 排序 from tb t
select 图二 = (select count(1) from tb where 图一 < t.图一) + 1 , 排序 from tb t
--sql 2005
select 图二 = row_number() over(partition by 排序 order by 图一), 排序 from tb t
select 图二 = row_number() over(order by 图一), 排序 from tb t
--更改
update tb
set 图二 = (select count(1) from tb where 排序 = t.排序 and 图一 < t.图一) + 1 , 排序 from tb t
row_number() over(order by 你要排序的字段) as 序号
if object_id('tb')>0
drop table tb
create table tb(id int,s nvarchar(20))
insert into tb
select 1,'设置'
union all
select 4,'设置'
union all
select 8,'设置'
union all
select 12,'设置'
select * from tb
select row_number() over(order by getdate()) as id ,s
from tb
order by s
--结果
1 设置
2 设置
3 设置
4 设置
CREATE TABLE #tp
(
id INT,
sort NVARCHAR(10)
)
INSERT INTO #tp SELECT 1,N'设置'
INSERT INTO #tp SELECT 4,N'设置'
INSERT INTO #tp SELECT 8,N'设置'
INSERT INTO #tp SELECT 12,N'设置'
SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE())new_id FROM #tp t
id sort new_id
----------- ---------- --------------------
1 设置 1
4 设置 2
8 设置 3
12 设置 4
(4 row(s) affected)
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[name] nvarchar(2))
Insert tb
select 1,N'设置' union all
select 4,N'设置' union all
select 8,N'设置' union all
select 12,N'设置'
Go
--2000
Select ID=(select count(*) from tb where id<=t.id),
[Name]
from tb t
---2005
select ID=row_number()over(order by id ),
[name]
from tb