• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

都来看,都来看,技术问题

想飞的狼 2008-03-23 02:08:27
有这样数据

票号 序号 保管员
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
...
要达到下面效果
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

重新整理票号和序号,把保管员相同的放到一个票号里,怎么做呢,我下了个可以实现效果不过速度特慢,看看哪位大哥还有好办法




...全文
66 点赞 收藏 14
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Limpire 2008-03-23
--> 测试数据: #
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
*/
回复
想飞的狼 2008-03-23
好使啦,没法子用自己的方法做的,谢谢大家支持,给分
回复
想飞的狼 2008-03-23
ddddddddddd
回复
想飞的狼 2008-03-23
票号 序号 保管员
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
A01C200801020001 1 52365
A01C200801020001 2 52365
A01C200801020001 3 52365
A01C200801020002 1 63212
A01C200801020002 2 63212
...
要达到下面效果
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


这样看,票号2008为年0101为哪月哪日,后四位为票号张数,现在每天相同保管员票号都出现好几张,想把一天相同保管员的票号更更新新成一张票号,序号自动累计,每天票号按12345...出现,也就是一天一个保管员只能在一张票号中出现.
回复
Limpire 2008-03-23
这种情况是什么处理规则?

select 'A01C200801010002',1,30012
select 'A01C200801020002',2,30012
select 'A01C200801030002',3,30012
回复
想飞的狼 2008-03-23
谢谢小楼回复,要是想update 怎么改
回复
Limpire 2008-03-23
--> 测试数据: #
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
*/
回复
想飞的狼 2008-03-23
dddddddddddddddddd
回复
Limpire 2008-03-23
1楼的方法依赖于数据的物理位置,如果是要更新数据,不能这么草率。
回复
想飞的狼 2008-03-23
我打错了,应该是每天都应该从1号票开始,请高手帮忙

票号 序号 保管员
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
...
要达到下面效果
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
回复
Limpire 2008-03-23
--> 测试数据: #
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
*/
回复
想飞的狼 2008-03-23
强人!!!!!!!!!!!!厉害,我要是更新一年的数据用上面的语名可以吗?




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 where substring(票号,5,4)='2008'

对不
回复
想飞的狼 2008-03-23
用UPDATE更新成想要的效果行吗?
回复
-狙击手- 2008-03-23
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 行)
*/
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-23 02:08
社区公告
暂无公告