bil_no voh_no sq_no voh_dd voh_flag
MK-201202-3-0001 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0002 MK-201202-3-0002 -0002 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0003 -0003 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0004 -0004 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0005 -0005 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0006 -0006 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0007 -0007 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0008 -0008 2012-02-13 14:22:43.070 转
MK-201202-3-0009 MK-201202-3-0009 -0009 2012-02-13 14:27:30.250 转
MK-201202-3-0010 MK-201202-3-0010 -0010 2012-02-13 14:27:35.930 转
另外个表:
ITM VOG_FLAG
1 现 现金凭证
2 银 银行凭证
3 转 转账凭证
就是说:要把voh_no 这个字段 以:MK-201202-01-0001 sq_no就是相对应的0001这样的格式显示
有意思的朋友可以来修改一下:
alter proc test
as
begin
update
a
set
voh_no=
'MK'+'-'+
convert(char(6),VOH_DD,112)+'-'+
right('01'+ltrim(b.[ITM]),2)+'-'+
right('0000'+ltrim(isnull((select count(1) from acc_voh c where convert(char(10),c.VOH_DD,112)=convert(char(10),a.VOH_DD,112)
and c.VOH_DD>=a.VOH_DD and c.voh_flag=a.voh_flag),0)) ,4),
sq_no=right('0000'+ltrim(isnull((select count(1) from acc_voh c where convert(char(10),c.VOH_DD,112)=convert(char(10),a.VOH_DD,112)
and c.VOH_DD>=a.VOH_DD and c.voh_flag=a.voh_flag),0)) ,4)
from
acc_voh a ,acc_voh_type b
where
a.voh_flag=b.voh_flag
end现在是这样写的。
MK-201202-3-0001 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0002 MK-201202-3-0002 -0002 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0003 -0003 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0004 -0004 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0005 -0005 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0006 -0006 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0007 -0007 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0008 -0008 2012-02-13 14:22:43.070 转
MK-201202-3-0009 MK-201202-3-0009 -0009 2012-02-13 14:27:30.250 转
MK-201202-3-0010 MK-201202-3-0010 -0010 2012-02-13 14:27:35.930 转
另外个表:
ITM VOG_FLAG
1 现 现金凭证
2 银 银行凭证
3 转 转账凭证
就是说:要把voh_no 这个字段 以:MK-201202-01-0001 sq_no就是相对应的0001这样的格式显示
有意思的朋友可以来修改一下:
alter proc test
as
begin
update
a
set
voh_no=
'MK'+'-'+
convert(char(6),VOH_DD,112)+'-'+
right('01'+ltrim(b.[ITM]),2)+'-'+
right('0000'+ltrim(isnull((select count(1) from acc_voh c where convert(char(10),c.VOH_DD,112)=convert(char(10),a.VOH_DD,112)
and c.VOH_DD>=a.VOH_DD and c.voh_flag=a.voh_flag),0)) ,4),
sq_no=right('0000'+ltrim(isnull((select count(1) from acc_voh c where convert(char(10),c.VOH_DD,112)=convert(char(10),a.VOH_DD,112)
and c.VOH_DD>=a.VOH_DD and c.voh_flag=a.voh_flag),0)) ,4)
from
acc_voh a ,acc_voh_type b
where
a.voh_flag=b.voh_flag
end现在是这样写的。
--测试数据
declare @acc_voh table
(
bil_no varchar(16),voh_no varchar(16),
sq_no varchar(5),voh_dd datetime,voh_flag varchar(10)
)
insert into @acc_voh
select 'MK-201202-3-0001','MK-201202-3-0001','0001','2012-02-13 13:54:10.987','转' union all
select 'MK-201202-3-0002','MK-201202-3-0002','-0002','2012-02-13 14:19:50.327','转' union all
select 'MK-201202-3-0003','MK-201202-3-0003','-0003','2012-02-13 14:19:50.387','转' union all
select 'MK-201202-3-0004','MK-201202-3-0004','-0004','2012-02-13 14:19:50.433','转' union all
select 'MK-201202-3-0005','MK-201202-3-0005','-0005','2012-02-13 14:19:50.507','转' union all
select 'MK-201202-3-0006','MK-201202-3-0006','-0006','2012-02-13 14:19:50.560','转' union all
select 'MK-201202-3-0007','MK-201202-3-0007','-0007','2012-02-13 14:22:31.780','转' union all
select 'MK-201202-3-0008','MK-201202-3-0008','-0008','2012-02-13 14:22:43.070','转' union all
select 'MK-201202-3-0009','MK-201202-3-0009','-0009','2012-02-13 14:27:30.250','转' union all
select 'MK-201202-3-0010','MK-201202-3-0010','-0010','2012-02-13 14:27:35.930','转'--更新
update @acc_voh set sq_no=parsename(replace(voh_no,'-','.'),1)
--查看
select * from @acc_voh
/*
bil_no voh_no sq_no voh_dd voh_flag
---------------- ---------------- ----- ----------------------- ----------
MK-201202-3-0001 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0002 MK-201202-3-0002 0002 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0003 0003 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0004 0004 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0005 0005 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0006 0006 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0007 0007 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0008 0008 2012-02-13 14:22:43.070 转
MK-201202-3-0009 MK-201202-3-0009 0009 2012-02-13 14:27:30.250 转
MK-201202-3-0010 MK-201202-3-0010 0010 2012-02-13 14:27:35.930 转
*/
as
begin
update
a
set
voh_no=
'MK'+'-'+
convert(char(6),VOH_DD,112)+'-'+
ltrim(b.[ITM])+'-'+
right(10000+(select count(1) from acc_voh where convert(varchar(10),VOH_DD,120)=convert(varchar(10),a.VOH_DD,120)
and VOH_DD<=a.VOH_DD and voh_flag=a.voh_flag) ,4),
sq_no=right(10000+(select count(1) from acc_voh where convert(varchar(10),VOH_DD,120)=convert(varchar(10),a.VOH_DD,120)
and VOH_DD<=a.VOH_DD and voh_flag=a.voh_flag) ,4)
from
acc_voh a ,acc_voh_type b
where
a.voh_flag=b.voh_flag
end
--测试数据
declare @acc_voh table
(
bil_no varchar(16),voh_no varchar(16),
sq_no varchar(5),voh_dd datetime,voh_flag varchar(10)
)
insert into @acc_voh
select 'MK-201202-3-0001','MK-201202-3-0001','0001','2012-02-13 13:54:10.987','转' union all
select 'MK-201202-3-0002','MK-201202-3-0002','-0002','2012-02-13 14:19:50.327','转' union all
select 'MK-201202-3-0003','MK-201202-3-0003','-0003','2012-02-13 14:19:50.387','转' union all
select 'MK-201202-3-0004','MK-201202-3-0004','-0004','2012-02-13 14:19:50.433','转' union all
select 'MK-201202-3-0005','MK-201202-3-0005','-0005','2012-02-13 14:19:50.507','转' union all
select 'MK-201202-3-0006','MK-201202-3-0006','-0006','2012-02-13 14:19:50.560','转' union all
select 'MK-201202-3-0007','MK-201202-3-0007','-0007','2012-02-13 14:22:31.780','转' union all
select 'MK-201202-3-0008','MK-201202-3-0008','-0008','2012-02-13 14:22:43.070','转' union all
select 'MK-201202-3-0009','MK-201202-3-0009','-0009','2012-02-13 14:27:30.250','转' union all
select 'MK-201202-3-0010','MK-201202-3-0010','-0010','2012-02-13 14:27:35.930','转'declare @acc_voh_type table (itm int,vog_flag varchar(2))
insert into @acc_voh_type
select 1,'现' union all
select 2,'银' union all
select 3,'转';with maco as
(
select row_number() over
(partition by convert(varchar(6),voh_dd,112) order by voh_dd) as num,* from @acc_voh
)update @acc_voh
set sq_no=right(10000+num,4),
voh_no='MK-'+convert(varchar(6),a.voh_dd,112)+'-'+ltrim(b.ITM)+'-'+right(10000+num,4)
from maco a left join @acc_voh_type b on a.voh_flag=b.vog_flagselect * from @acc_voh
/*
bil_no voh_no sq_no voh_dd voh_flag
---------------- ---------------- ----- ----------------------- ----------
MK-201202-3-0001 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0002 MK-201202-3-0010 0010 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0001 0001 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0010 0010 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0001 0001 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0010 0010 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0001 0001 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0010 0010 2012-02-13 14:22:43.070 转
MK-201202-3-0009 MK-201202-3-0001 0001 2012-02-13 14:27:30.250 转
MK-201202-3-0010 MK-201202-3-0010 0010 2012-02-13 14:27:35.930 转
*/
--刚才的结果不对,修正一下
--测试数据
declare @acc_voh table
(
bil_no varchar(16),voh_no varchar(16),
sq_no varchar(5),voh_dd datetime,voh_flag varchar(10)
)
insert into @acc_voh
select 'MK-201202-3-0001','MK-201202-3-0001','0001','2012-02-13 13:54:10.987','转' union all
select 'MK-201202-3-0002','MK-201202-3-0002','-0002','2012-02-13 14:19:50.327','转' union all
select 'MK-201202-3-0003','MK-201202-3-0003','-0003','2012-02-13 14:19:50.387','转' union all
select 'MK-201202-3-0004','MK-201202-3-0004','-0004','2012-02-13 14:19:50.433','转' union all
select 'MK-201202-3-0005','MK-201202-3-0005','-0005','2012-02-13 14:19:50.507','转' union all
select 'MK-201202-3-0006','MK-201202-3-0006','-0006','2012-02-13 14:19:50.560','转' union all
select 'MK-201202-3-0007','MK-201202-3-0007','-0007','2012-02-13 14:22:31.780','转' union all
select 'MK-201202-3-0008','MK-201202-3-0008','-0008','2012-02-13 14:22:43.070','转' union all
select 'MK-201202-3-0009','MK-201202-3-0009','-0009','2012-02-13 14:27:30.250','转' union all
select 'MK-201202-3-0010','MK-201202-3-0010','-0010','2012-02-13 14:27:35.930','转'declare @acc_voh_type table (itm int,vog_flag varchar(2))
insert into @acc_voh_type
select 1,'现' union all
select 2,'银' union all
select 3,'转';with maco as
(
select row_number() over
(partition by convert(varchar(6),voh_dd,112) order by voh_dd) as num,* from @acc_voh
)
update @acc_voh
set sq_no=right(10000+num,4),
voh_no='MK-'+convert(varchar(6),a.voh_dd,112)+'-'+ltrim(b.ITM)+'-'+right(10000+num,4)
from @acc_voh c left join maco a on c.bil_no=a.bil_no
left join @acc_voh_type b on a.voh_flag=b.vog_flagselect * from @acc_voh
/*
bil_no voh_no sq_no voh_dd voh_flag
---------------- ---------------- ----- ----------------------- ----------
MK-201202-3-0001 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0002 MK-201202-3-0002 0002 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0003 0003 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0004 0004 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0005 0005 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0006 0006 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0007 0007 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0008 0008 2012-02-13 14:22:43.070 转
MK-201202-3-0009 MK-201202-3-0009 0009 2012-02-13 14:27:30.250 转
MK-201202-3-0010 MK-201202-3-0010 0010 2012-02-13 14:27:35.930 转
*/
declare @acc_voh table
(
bil_no varchar(16),voh_no varchar(16),
sq_no varchar(5),voh_dd datetime,voh_flag varchar(10)
)
insert into @acc_voh
select 'MK-201202-3-0001','MK-201202-3-0001','0001','2012-02-13 13:54:10.987','转' union all
select 'MK-201202-3-0002','MK-201202-3-0002','-0002','2012-02-13 14:19:50.327','转' union all
select 'MK-201202-3-0003','MK-201202-3-0003','-0003','2012-02-13 14:19:50.387','转' union all
select 'MK-201202-3-0004','MK-201202-3-0004','-0004','2012-02-13 14:19:50.433','转' union all
select 'MK-201202-3-0005','MK-201202-3-0005','-0005','2012-02-13 14:19:50.507','转' union all
select 'MK-201202-3-0006','MK-201202-3-0006','-0006','2012-02-13 14:19:50.560','转' union all
select 'MK-201202-3-0007','MK-201202-3-0007','-0007','2012-02-13 14:22:31.780','转' union all
select 'MK-201202-3-0008','MK-201202-3-0008','-0008','2012-02-13 14:22:43.070','转' union all
select 'MK-201202-3-0009','MK-201202-3-0009','-0009','2012-02-13 14:27:30.250','转' union all
select 'MK-201202-3-0010','MK-201202-3-0010','-0010','2012-02-13 14:27:35.930','转'declare @acc_voh_type table (itm int,vog_flag varchar(2))
insert into @acc_voh_type
select 1,'现' union all
select 2,'银' union all
select 3,'转';with maco as
(
select row_number() over (partition by convert(varchar(6),voh_dd,112) order by voh_dd) as num,a.*,b.itm
from @acc_voh a,@acc_voh_type b where a.voh_flag=b.vog_flag
)
update @acc_voh set sq_no=right(10000+num,4),
voh_no='MK-'+convert(varchar(6),a.voh_dd,112)+'-'+ltrim(ITM)+'-'+right(10000+num,4)
from @acc_voh c , maco a where c.bil_no=a.bil_noselect * from @acc_voh
无法对 'test' 进行更改,因为它是不兼容的对象类型。
我用的是2005不知道怎么老报这个错误
参考一下这个:
http://xyztony1985.blog.163.com/blog/static/361178201193125215547/
MK-201202-3-0002 MK-201202-3-0002 -0002 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0003 -0003 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0004 -0004 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0005 -0005 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0006 -0006 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0007 -0007 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0008 -0008 2012-02-13 14:22:43.070 转
请看
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-02-22 10:55:22
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[acc_voh]
if object_id('[acc_voh]') is not null drop table [acc_voh]
go
create table [acc_voh]([bil_no] varchar(16),[voh_no] varchar(16),[sq_no] varchar(5),[voh_dd] datetime,[voh_flag] varchar(2))
insert [acc_voh]
select 'MK-201202-3-0001','MK-201202-3-0001','0001','2012-02-13 13:54:10.987','转' union all
select 'MK-201202-3-0002','MK-201202-3-0002','-0002','2012-02-13 14:19:50.327','转' union all
select 'MK-201202-3-0003','MK-201202-3-0003','-0003','2012-02-13 14:19:50.387','转' union all
select 'MK-201202-3-0004','MK-201202-3-0004','-0004','2012-02-13 14:19:50.433','转' union all
select 'MK-201202-3-0005','MK-201202-3-0005','-0005','2012-02-13 14:19:50.507','转' union all
select 'MK-201202-3-0006','MK-201202-3-0006','-0006','2012-02-13 14:19:50.560','转' union all
select 'MK-201202-3-0007','MK-201202-3-0007','-0007','2012-02-13 14:22:31.780','转' union all
select 'MK-201202-3-0008','MK-201202-3-0008','-0008','2012-02-13 14:22:43.070','转' union all
select 'MK-201202-3-0009','MK-201202-3-0009','-0009','2012-02-13 14:27:30.250','转' union all
select 'MK-201202-3-0010','MK-201202-3-0010','-0010','2012-02-13 14:27:35.930','转'
--> 测试数据:[acc_voh_type]
if object_id('[acc_voh_type]') is not null drop table [acc_voh_type]
go
create table [acc_voh_type]([ITM] int,[VOH_FLAG] varchar(2),[FLAG_NOTE] varchar(8))
insert [acc_voh_type]
select 1,'现','现金凭证' union all
select 2,'银','银行凭证' union all
select 3,'转','转账凭证'
--------------开始查询--------------------------
if object_id('test') is not null
drop proc test
go
create proc test
as
begin
update
a
set
voh_no=
'MK'+'-'+
convert(char(6),VOH_DD,112)+'-'+
ltrim(b.[ITM])+'-'+
right('0000'+ltrim(isnull((select count(1) from acc_voh c where
c.bil_no=a.bil_no and c.voh_flag=a.voh_flag),0)) ,4), sq_no=REPLACE(sq_no,'-','')
from
acc_voh a ,acc_voh_type b
where
a.voh_flag=b.voh_flag
end
go
exec test
go
select * from [acc_voh]----------------结果----------------------------
/* bil_no voh_no sq_no voh_dd voh_flag
---------------- ---------------- ----- ----------------------- --------
MK-201202-3-0001 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0002 MK-201202-3-0001 0002 2012-02-13 14:19:50.327 转
MK-201202-3-0003 MK-201202-3-0001 0003 2012-02-13 14:19:50.387 转
MK-201202-3-0004 MK-201202-3-0001 0004 2012-02-13 14:19:50.433 转
MK-201202-3-0005 MK-201202-3-0001 0005 2012-02-13 14:19:50.507 转
MK-201202-3-0006 MK-201202-3-0001 0006 2012-02-13 14:19:50.560 转
MK-201202-3-0007 MK-201202-3-0001 0007 2012-02-13 14:22:31.780 转
MK-201202-3-0008 MK-201202-3-0001 0008 2012-02-13 14:22:43.070 转
MK-201202-3-0009 MK-201202-3-0001 0009 2012-02-13 14:27:30.250 转
MK-201202-3-0010 MK-201202-3-0001 0010 2012-02-13 14:27:35.930 转(10 行受影响)
*/