编号的问题
名称 日期 编号
a 2007-1-6
b 2007-2-8
c 2007-1-6
d 2001-1-6
e 2007-1-6
f 2007-1-9
g 2007-2-8
把编号写入
名称 日期 编号
a 2007-1-6 200701060001
b 2007-2-8 200702080001
c 2007-1-6 200701060002
d 2001-1-6 200701060003
e 2007-1-6 200701060004
f 2007-1-9 200701090001
g 2007-2-8 200702080002
[解决办法]
select 名称,日期,编号=convert(varchar(10),日期,120)+right( '0000 '+(select ltrim(count(1)) from [Table] where datediff(day,日期,a.日期)=0 and 名称 <=a.名称),4) from [Table] a
[解决办法]
create table tb (名称 varchar(10),日期 datetime,编号 varchar(12))
insert into tb values( 'a ', '2007-1-6 ', ' ')
insert into tb values( 'b ', '2007-2-8 ', ' ')
insert into tb values( 'c ', '2007-1-6 ', ' ')
insert into tb values( 'd ', '2007-1-6 ', ' ')
insert into tb values( 'e ', '2007-1-6 ', ' ')
insert into tb values( 'f ', '2007-1-9 ', ' ')
insert into tb values( 'g ', '2007-2-8 ', ' ')
go
select 名称,convert(varchar(10),日期,120) 日期,convert(varchar(8),日期,112)+right( '0000 ' + cast(编号 as varchar),4) 编号 from
(
select 编号=(select count(1) from tb where convert(varchar(8),日期,112)=convert(varchar(8),a.日期,112) and 名称 <a.名称)+1 , 名称,日期 from tb a
) t
drop table tb
/*
名称 日期 编号
---------- ---------- ----------------
a 2007-01-06 200701060001
b 2007-02-08 200702080001
c 2007-01-06 200701060002
d 2007-01-06 200701060003
e 2007-01-06 200701060004
f 2007-01-09 200701090001
g 2007-02-08 200702080002
（所影响的行数为 7 行）
*/
[解决办法]
declare @a table(名称 varchar(10), 日期 smalldatetime, 编号 varchar(100))
insert @a select 'a ', '2007-1-6 ',null
union all select 'b ', '2007-2-8 ',null
union all select 'c ', '2007-1-6 ',null
union all select 'd ', '2001-1-6 ',null
union all select 'e ', '2007-1-6 ',null
union all select 'f ', '2007-1-9 ',null
union all select 'g ', '2007-2-8 ',null
update @a set 编号=convert(varchar(10),日期,112)+right( '0000 '+(select ltrim(count(1)) from @a where datediff(day,日期,a.日期)=0 and 名称 <=a.名称),4) from @a a
[解决办法]
create table tb (名称 varchar(10),日期 datetime,编号 varchar(12))
insert into tb values( 'a ', '2007-1-6 ', ' ')
insert into tb values( 'b ', '2007-2-8 ', ' ')
insert into tb values( 'c ', '2007-1-6 ', ' ')
insert into tb values( 'd ', '2007-1-6 ', ' ')
insert into tb values( 'e ', '2007-1-6 ', ' ')
insert into tb values( 'f ', '2007-1-9 ', ' ')
insert into tb values( 'g ', '2007-2-8 ', ' ')
go
update tb
set 编号 = convert(varchar(8),tb.日期,112)+right( '0000 ' + cast(t.编号 as varchar),4)
from tb,(select 编号=(select count(1) from tb where convert(varchar(8),日期,112)=convert(varchar(8),a.日期,112) and 名称 <a.名称)+1 , 名称,日期 from tb a) t
where tb.名称 = t.名称 and tb.日期 = t.日期
select * from tb
drop table tb
/*
名称 日期 编号
---------- ------------------------------------------------------ ------------
a 2007-01-06 00:00:00.000 200701060001
b 2007-02-08 00:00:00.000 200702080001
c 2007-01-06 00:00:00.000 200701060002
d 2007-01-06 00:00:00.000 200701060003
e 2007-01-06 00:00:00.000 200701060004
f 2007-01-09 00:00:00.000 200701090001
g 2007-02-08 00:00:00.000 200702080002
（所影响的行数为 7 行）
*/
[解决办法]
create table t(名称 varchar(100),日期 datetime,编号 varchar(100))
insert into t select 'a ', '2007-1-6 ',null
insert into t select 'b ', '2007-2-8 ',null
insert into t select 'c ', '2007-1-6 ',null
insert into t select 'd ', '2007-1-6 ',null
insert into t select 'e ', '2007-1-6 ',null
insert into t select 'f ', '2007-1-9 ',null
insert into t select 'g ', '2007-2-8 ',null
update T set 编号=convert(varchar(8),日期,112) + right( cast((select count(*) from t where 日期=a.日期 and 名称 <=a.名称) as varchar(100)),3)
from T as a
select * from T
drop table T