34,576
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: #
if object_id('tempdb.dbo.#') is not null drop table #
create table # (票号 varchar(16),序号 int,保管员 int)
insert into #
select 'A01C200801010001',1,30012 union all
select 'A01C200801010001',2,30012 union all
select 'A01C200801010002',1,30012 union all
select 'A01C200801020002',2,30012 union all -- 改动
select 'A01C200801030002',3,30012 union all -- 改动
select 'A01C200801010003',1,30012 union all
select 'A01C200801010003',2,30012 union all
select 'A01C200801010003',3,30012 union all
select 'A01C200801010004',1,30012 union all
select 'A01C200801010004',2,30012 union all
select 'A01C200801020005',1,52365 union all
select 'A01C200801020005',2,52365 union all
select 'A01C200801020005',3,52365 union all
select 'A01C200801020006',1,63212 union all
select 'A01C200801020007',2,63212
;
-- 看看结果对不对,不对白搭。你是不是用2005,用2000也白搭
with T as
(
select id1=dense_rank() over (partition by left(票号,12) order by 保管员),id2=row_number() over (partition by left(票号,12),保管员 order by 票号),* from #
)
select 票号=left(票号,12)+replicate('0',4-len(id1))+ltrim(id1),序号=id2,保管员 from T
/*
票号 序号 保管员
------------------- -------------------- -----------
A01C200801010001 1 30012
A01C200801010001 2 30012
A01C200801010001 3 30012
A01C200801010001 4 30012
A01C200801010001 5 30012
A01C200801010001 6 30012
A01C200801010001 7 30012
A01C200801010001 8 30012
A01C200801020001 1 30012
A01C200801020002 1 52365
A01C200801020002 2 52365
A01C200801020002 3 52365
A01C200801020003 1 63212
A01C200801020003 2 63212
A01C200801030001 1 30012
*/
--> 测试数据: #
if object_id('tempdb.dbo.#') is not null drop table #
create table # (票号 varchar(16),序号 int,保管员 int)
insert into #
select 'A01C200801010001',1,30012 union all
select 'A01C200801010001',2,30012 union all
select 'A01C200801010002',1,30012 union all
select 'A01C200801010002',2,30012 union all
select 'A01C200801010002',3,30012 union all
select 'A01C200801010003',1,30012 union all
select 'A01C200801010003',2,30012 union all
select 'A01C200801010003',3,30012 union all
select 'A01C200801010004',1,30012 union all
select 'A01C200801010004',2,30012 union all
select 'A01C200801020005',1,52365 union all
select 'A01C200801020005',2,52365 union all
select 'A01C200801020005',3,52365 union all
select 'A01C200801020006',1,63212 union all
select 'A01C200801020007',2,63212
;
-- 每天都应该从1号票开始
with T as
(
select id1=dense_rank() over (partition by left(票号,12) order by 保管员),id2=row_number() over (partition by 保管员 order by 票号),* from #
)
select 票号=left(票号,12)+replicate('0',4-len(id1))+ltrim(id1),序号=id2,保管员 from T
/*
票号 序号 保管员
---------------- -------------------- -----------
A01C200801010001 1 30012
A01C200801010001 2 30012
A01C200801010001 3 30012
A01C200801010001 4 30012
A01C200801010001 5 30012
A01C200801010001 6 30012
A01C200801010001 7 30012
A01C200801010001 8 30012
A01C200801010001 9 30012
A01C200801010001 10 30012
A01C200801020001 1 52365
A01C200801020001 2 52365
A01C200801020001 3 52365
A01C200801020002 1 63212
A01C200801020002 2 63212
*/
--> 测试数据: #
if object_id('tempdb.dbo.#') is not null drop table #
create table # (票号 varchar(16),序号 int,保管员 int)
insert into #
select 'A01C200801010001',1,30012 union all
select 'A01C200801010001',2,30012 union all
select 'A01C200801010002',1,30012 union all
select 'A01C200801010002',2,30012 union all
select 'A01C200801010002',3,30012 union all
select 'A01C200801010003',1,30012 union all
select 'A01C200801010003',2,30012 union all
select 'A01C200801010003',3,30012 union all
select 'A01C200801010004',1,30012 union all
select 'A01C200801010004',2,30012 union all
select 'A01C200801020005',1,52365 union all
select 'A01C200801020005',2,52365 union all
select 'A01C200801020005',3,52365 union all
select 'A01C200801020006',1,63212 union all
select 'A01C200801020007',2,63212
;
--> 2005
with T as
(
select id1=dense_rank() over (order by 保管员),id2=row_number() over (partition by 保管员 order by 票号),* from #
)
select 票号=left(票号,12)+replicate('0',4-len(id1))+ltrim(id1),序号=id2,保管员 from T
/*
票号 序号 保管员
---------------- -------------------- -----------
A01C200801010001 1 30012
A01C200801010001 2 30012
A01C200801010001 3 30012
A01C200801010001 4 30012
A01C200801010001 5 30012
A01C200801010001 6 30012
A01C200801010001 7 30012
A01C200801010001 8 30012
A01C200801010001 9 30012
A01C200801010001 10 30012
A01C200801020002 1 52365
A01C200801020002 2 52365
A01C200801020002 3 52365
A01C200801020003 1 63212
A01C200801020003 2 63212
*/
declare @t table(票号 varchar(20),序号 int,保管员 int)
insert @t select
'A01C200801010001',1,30012 union select
'A01C200801010001',2,30012 union select
'A01C200801010002',1,30012 union select
'A01C200801010002',2,30012 union select
'A01C200801010002',3,30012 union select
'A01C200801010003',1,30012 union select
'A01C200801010003',2,30012 union select
'A01C200801010003',3,30012 union select
'A01C200801010004',1,30012 union select
'A01C200801010004',2,30012 union select
'A01C200801020005',1,52365 union select
'A01C200801020005',2,52365 union select
'A01C200801020005',3,52365 union select
'A01C200801020006',1,63212 union select
'A01C200801020007',2,63212
select * from @t
declare @i int,@b int,@px int
set @I = 0
set @px = 0
update a
set 序号 = @i,
票号 = left(票号,12)+ right('0000'+ltrim(@px),4),
@I = case when 保管员 = @b then @i else 0 end + 1,
@px = @px + case when 保管员 = @b then 0 else 1 end,
@b = a.保管员
from @t a
select * from @t
/*
(所影响的行数为 15 行)
票号 序号 保管员
-------------------- ----------- -----------
A01C200801010001 1 30012
A01C200801010001 2 30012
A01C200801010002 1 30012
A01C200801010002 2 30012
A01C200801010002 3 30012
A01C200801010003 1 30012
A01C200801010003 2 30012
A01C200801010003 3 30012
A01C200801010004 1 30012
A01C200801010004 2 30012
A01C200801020005 1 52365
A01C200801020005 2 52365
A01C200801020005 3 52365
A01C200801020006 1 63212
A01C200801020007 2 63212
(所影响的行数为 15 行)
(所影响的行数为 15 行)
票号 序号 保管员
-------------------- ----------- -----------
A01C200801010001 1 30012
A01C200801010001 2 30012
A01C200801010001 3 30012
A01C200801010001 4 30012
A01C200801010001 5 30012
A01C200801010001 6 30012
A01C200801010001 7 30012
A01C200801010001 8 30012
A01C200801010001 9 30012
A01C200801010001 10 30012
A01C200801020002 1 52365
A01C200801020002 2 52365
A01C200801020002 3 52365
A01C200801020003 1 63212
A01C200801020003 2 63212
(所影响的行数为 15 行)
*/