22,209
社区成员
发帖
与我相关
我的任务
分享
Declare @ID int
Declare @Adate datetime
set @ID=1
Set @Adate='2013-07-24'
Declare @table1 table (SNID int ,SNFormat varchar(50))
Declare @table2 table(SNID int, MaxNo int)
insert @table1
select 1,N'"CK" - YYYY - MM - DD - [000]' union all
select 2,N'"WWL" - YYYY - MM - DD - [000]' union all
select 3,N'"WWT" - YYYY - MM - DD - [000]' union all
select 4,N'"SK" - yyyy - mm - dd - [000]' union all
select 5,N'"DF" - yyyy - mm - dd - [000]' union all
select 6,N'"JT" - yyyy - mm - dd - [000]' union all
select 7,N'"JHD" - yyyy - mm - dd - [000]'
insert @table2
select 1, 5 union all
select 2, 4 union all
select 3, 6 union all
select 4, 111 union all
select 5, 0 union all
select 6, 1 union all
select 7, 20
select t1.SNID,t2.MaxNo,t1.SNFormat
,AfterReplace=Replace(Replace(t1.SNFormat,' ',''),'"','')
,FormatStr= Replace(Replace(Replace(Replace(t1.SNFormat,' ',''),'"',''),'yyyy-mm-dd',convert(char(10),@Adate,120)),'[000]',right('000'+cast(t2.MaxNo+1 as varchar),3))
from @table1 t1
join @table2 t2 on t1.SNID= t2.SNID
--where t1.SNID=@ID
/*
SNID MaxNo SNFormat AfterReplace FormatStr
1 5 "CK" - YYYY - MM - DD - [000] CK-YYYY-MM-DD-[000] CK-2013-07-24-006
2 4 "WWL" - YYYY - MM - DD - [000] WWL-YYYY-MM-DD-[000] WWL-2013-07-24-005
3 6 "WWT" - YYYY - MM - DD - [000] WWT-YYYY-MM-DD-[000] WWT-2013-07-24-007
4 111 "SK" - yyyy - mm - dd - [000] SK-yyyy-mm-dd-[000] SK-2013-07-24-112
5 0 "DF" - yyyy - mm - dd - [000] DF-yyyy-mm-dd-[000] DF-2013-07-24-001
6 1 "JT" - yyyy - mm - dd - [000] JT-yyyy-mm-dd-[000] JT-2013-07-24-002
7 20 "JHD" - yyyy - mm - dd - [000] JHD-yyyy-mm-dd-[000] JHD-2013-07-24-021
*/
sql脚本如下:
[code=sql]
if exists (select * from sysobjects where id = OBJECT_ID('[Bill]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Bill]
CREATE TABLE [Bill] (
[SNID] [int] NOT NULL,
[MaxNo] [int] NULL)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 1,5)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 2,4)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 3,6)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 4,111)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 5,0)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 6,1)
INSERT [Bill] ([SNID],[MaxNo]) VALUES ( 7,20)
if exists (select * from sysobjects where id = OBJECT_ID('[SN]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [SN]
CREATE TABLE [SN] (
[SNID] [int] NOT NULL,
[SNFormat] [varchar] (1000) NULL)
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 1,N'"CK" - YYYY - MM - DD - [000]')
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 2,N'"WWL" - YYYY - MM - DD - [000]')
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 3,N'"WWT" - YYYY - MM - DD - [000]')
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 4,N'"SK" - yyyy - mm - dd - [000]')
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 5,N'"DF" - yyyy - mm - dd - [000]')
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 6,N'"JT" - yyyy - mm - dd - [000]')
INSERT [SN] ([SNID],[SNFormat]) VALUES ( 7,N'"JHD" - yyyy - mm - dd - [000]')
你得到的数据是
"CK" - YYYY - MM - DD - 006
"WWL" - YYYY - MM - DD - 005
"WWT" - YYYY - MM - DD - 007
"SK" - yyyy - mm - dd - 112
"DF" - yyyy - mm - dd - 001
"JT" - yyyy - mm - dd - 002
"JHD" - yyyy - mm - dd - 021
我想要的是这个
CK-2013-07-24-006
WWL-2013-07-24-005
WWT-2013-07-24-007
SK-2013-07-24-112
DF-2013-07-24-001
JT-2013-07-24-002
JHD-2013-07-24-021
需要处理双引号,和日期
[/code]
Declare @ID int
Declare @Adate datetime
set @ID=1
Set @Adate='2013-07-24'
Declare @table1 table (SNID int ,SNFormat varchar(50))
Declare @table2 table(SNID int, MaxNo int)
insert @table1
select 1,'CK-YYYY-MM-DD-[000]' union all
select 2,'WWL-YYYY-MM-DD-[000]' union all
select 3,'WWT-YYYY-MM-DD-[000]' union all
select 4,'SK-yyyy-mm-dd-[000]' union all
select 5,'DF-yyyy-mm-dd-[000]' union all
select 6,'JT-yyyy-mm-dd-[000]' union all
select 7,'JHD-yyyy-mm-dd-[000]'
insert @table2
select 1, 5 union all
select 2, 4 union all
select 3, 6 union all
select 4, 11 union all
select 5, 0 union all
select 6, 1 union all
select 7, 20
select FormatStr= Replace(Replace(t1.SNFormat ,'yyyy-mm-dd',convert(char(10),@Adate,120)),'[000]',right('000'+cast(t2.MaxNo+1 as varchar),3))
from @table1 t1
join @table2 t2 on t1.SNID= t2.SNID
--where t1.SNID=@ID