fxh FNumber fxh FNumber
1 2312220102010001 1 23122201020100011
2 2312220102010001 2 23122201020100012
3 2312220102010002 3 23122201020100021
4 2312220102010003 4 23122201020100031
5 2312220102010003 5 23122201020100032
6 2312220102010004 6 23122201020100041
7 2312220102010005 7 23122201020100051
8 2312220102010005 8 23122201020100052
9 2312220102010005 9 23122201020100053
10 2312220102010005 10 23122201020100054
... ...
数据很多
1 2312220102010001 1 23122201020100011
2 2312220102010001 2 23122201020100012
3 2312220102010002 3 23122201020100021
4 2312220102010003 4 23122201020100031
5 2312220102010003 5 23122201020100032
6 2312220102010004 6 23122201020100041
7 2312220102010005 7 23122201020100051
8 2312220102010005 8 23122201020100052
9 2312220102010005 9 23122201020100053
10 2312220102010005 10 23122201020100054
... ...
数据很多
解决方案 »
- 远程数据操作用链接服务器是否能提高速度?
- access转sql后 发布新闻时,日期可以正常发布,但是 不能修改 成别的日期 是什么原因
- SQL Server2000数据库还原的时候,如何清空备份纪录?(详情请进)
- 救命啊 我想活下去 (在线等)
- 请问:是什么原因造成 SQL2000(打补丁的个人版)数据库显示置疑?
- 安装 sql 2008 时出错,提示 SQLBrowser”启动请求失败
- [求教]关于数据库封锁协议!~~~~
- 关于一个系统方案的问题
- select出的sum能查出来,但一直提示Order by clause 不能使用此查询类型
- 如何去除字段中的数字
- 64位系统备份sql32位系统下还原失败
- VC++6.0中SQL 开发问题
tb
set
FNumber= FNumber+ltrim(ltrim(row_number() over(partition by FNumber order by getdate()))
insert into tb select 1,'2312220102010001'
insert into tb select 2,'2312220102010001'
insert into tb select 3,'2312220102010002'
insert into tb select 4,'2312220102010003'
insert into tb select 5,'2312220102010003'
insert into tb select 6,'2312220102010004'
insert into tb select 7,'2312220102010005'
insert into tb select 8,'2312220102010005'
insert into tb select 9,'2312220102010005'
insert into tb select 10,'2312220102010005'
go
select fxh,fnumber+ltrim(rn) fnumber from(
select *,row_number()over(partition by fnumber order by (select 1))rn from tb
)t
/*
fxh fnumber
----------- --------------------------------------------
1 23122201020100011
2 23122201020100012
3 23122201020100021
4 23122201020100031
5 23122201020100032
6 23122201020100041
7 23122201020100051
8 23122201020100052
9 23122201020100053
10 23122201020100054(10 行受影响)*/
go
drop table tb
create table tb(fxh int,FNumber nvarchar(20))
insert into tb select 1,'2312220102010001'
insert into tb select 2,'2312220102010001'
insert into tb select 3,'2312220102010002'
insert into tb select 4,'2312220102010003'
insert into tb select 5,'2312220102010003'
insert into tb select 6,'2312220102010004'
insert into tb select 7,'2312220102010005'
insert into tb select 8,'2312220102010005'
insert into tb select 9,'2312220102010005'
insert into tb select 10,'2312220102010005'
go
select fxh,fnumber+ltrim(rn) fnumber from(
select *,row_number()over(partition by fnumber order by fxh)rn from tb
)t
/*
fxh fnumber
----------- --------------------------------------------
1 23122201020100011
2 23122201020100012
3 23122201020100021
4 23122201020100031
5 23122201020100032
6 23122201020100041
7 23122201020100051
8 23122201020100052
9 23122201020100053
10 23122201020100054(10 行受影响)*/
go
drop table tb
create table tb(fxh int,FNumber nvarchar(20))
insert into tb select 1,'2312220102010001'
insert into tb select 2,'2312220102010001'
insert into tb select 3,'2312220102010002'
insert into tb select 4,'2312220102010003'
insert into tb select 5,'2312220102010003'
insert into tb select 6,'2312220102010004'
insert into tb select 7,'2312220102010005'
insert into tb select 8,'2312220102010005'
insert into tb select 9,'2312220102010005'
insert into tb select 10,'2312220102010005'
go
update a set FNumber=Fnumber+ltrim(b.rn) from tb a inner join(
select fxh,row_number()over(partition by fnumber order by fxh)rn from tb
)b on a.fxh=b.fxh
select * from tb
/*
fxh fnumber
----------- --------------------------------------------
1 23122201020100011
2 23122201020100012
3 23122201020100021
4 23122201020100031
5 23122201020100032
6 23122201020100041
7 23122201020100051
8 23122201020100052
9 23122201020100053
10 23122201020100054(10 行受影响)*/
go
drop table tb
(
fxh INT
, FNumber CHAR(16)
)INSERT INTO TEST
SELECT 1, '2312220102010001'
UNION ALL
SELECT 2, '2312220102010001'
UNION ALL
SELECT 3, '2312220102010002'
UNION ALL
SELECT 4, '2312220102010003'
UNION ALL
SELECT 5, '2312220102010003'
UNION ALL
SELECT 6, '2312220102010004'
UNION ALL
SELECT 7, '2312220102010005'
UNION ALL
SELECT 8, '2312220102010005'
UNION ALL
SELECT 9, '2312220102010005'
UNION ALL
SELECT 10, '2312220102010005'SELECT fxh
, FNumber + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (PARTITION BY FNumber ORDER BY fxh)) AS FNumber
FROM TESTDROP TABLE TEST