UPDATE 表
SET dh =
(SELECT COUNT(*)
FROM 表
WHERE datediff(month, a.rq, rq) = 0 AND rq <= a.rq)
from 表 a
SET dh =
(SELECT COUNT(*)
FROM 表
WHERE datediff(month, a.rq, rq) = 0 AND rq <= a.rq)
from 表 a
+(SELECT COUNT(*) FROM 表 WHERE datediff(month,a.rq,rq)=0 AND rq <= a.rq),4)
from 表 a
rk dh
1933/04/22 RK33040002
1933/04/22 RK33040002
RK33040002 RK00010001
2001/04/03 RK01040003
2001/04/03 RK01040003
2001/04/03 RK01040003
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/05 RK01040015
2001/04/05 RK01040015
2001/04/05 RK01040015
2001/04/05 RK01040015
2001/04/06 RK01040023
.
.
.
这种情况,为什么?
格式是这样的,我想序号是每年每月重新从一开始,如:RK01040001, RK01040002...下月就是RK01050001 RK01050002
SET rkdh = 'RK' + CONVERT(char(4), rkrq, 12) + RIGHT(10000 +
(SELECT COUNT(*)
FROM rk_bak
WHERE datediff(month, a.rkrq, rkrq) = 0 AND rkrq <= a.rkrq), 4)
FROM rk_bak a
我是这么做的
1933/04/22 RK33040002
1933/04/22 RK33040002
2000/01/01 RK00010001
2001/04/03 RK01040003
2001/04/03 RK01040003
2001/04/03 RK01040003
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/04 RK01040011
2001/04/05 RK01040015
2001/04/05 RK01040015
2001/04/05 RK01040015
2001/04/05 RK01040015
2001/04/06 RK01040023
declare @rq datetime,@i int
update rk_bak set @i=case datediff(month,rkrq,@rq) when 0 then @i+1 else 10001 end
,rkdh='RK'+CONVERT(char(4),rkrq,12)+right(@i,4)
,@rq=rkrq
create table rk_bak(rkrq datetime,rkdh char(10))
insert rk_bak(rkrq)
select '1933/04/22'
union all select '1933/04/22'
union all select '2000/01/01'
union all select '2001/04/03'
union all select '2001/04/03'
union all select '2001/04/03'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/05'
union all select '2001/04/05'
union all select '2001/04/05'
union all select '2001/04/05'
union all select '2001/04/06'
go--更新处理
declare @rq datetime,@i int
update rk_bak set @i=case datediff(month,rkrq,@rq) when 0 then @i+1 else 10001 end
,rkdh='RK'+CONVERT(char(4),rkrq,12)+right(@i,4)
,@rq=rkrq
go--显示处理结果
select * from rk_bak
go--删除测试
drop table rk_bak/*--测试结果rkrq rkdh
------------------------------------------------------ ----------
1933-04-22 00:00:00.000 RK33040001
1933-04-22 00:00:00.000 RK33040002
2000-01-01 00:00:00.000 RK00010001
2001-04-03 00:00:00.000 RK01040001
2001-04-03 00:00:00.000 RK01040002
2001-04-03 00:00:00.000 RK01040003
2001-04-04 00:00:00.000 RK01040004
2001-04-04 00:00:00.000 RK01040005
2001-04-04 00:00:00.000 RK01040006
2001-04-04 00:00:00.000 RK01040007
2001-04-04 00:00:00.000 RK01040008
2001-04-04 00:00:00.000 RK01040009
2001-04-04 00:00:00.000 RK01040010
2001-04-04 00:00:00.000 RK01040011
2001-04-05 00:00:00.000 RK01040012
2001-04-05 00:00:00.000 RK01040013
2001-04-05 00:00:00.000 RK01040014
2001-04-05 00:00:00.000 RK01040015
2001-04-06 00:00:00.000 RK01040016(所影响的行数为 19 行)
--*/
+right(10000+(
select count(*) from rk_bak
where datediff(month,rkrq,a.rkrq)=0 and rkrq<a.rkrq
or(rkrq=a.rkrq and id<=a.id)
),4)
from rk_bak a
create table rk_bak(id int identity(1,1),rkrq datetime,rkdh char(10))
insert rk_bak(rkrq)
select '1933/04/22'
union all select '1933/04/22'
union all select '2000/01/01'
union all select '2001/04/03'
union all select '2001/04/03'
union all select '2001/04/03'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/04'
union all select '2001/04/05'
union all select '2001/04/05'
union all select '2001/04/05'
union all select '2001/04/05'
union all select '2001/04/06'
go--更新处理
update rk_bak set rkdh='RK'+CONVERT(char(4),rkrq,12)
+right(10000+(
select count(*) from rk_bak
where datediff(month,rkrq,a.rkrq)=0 and rkrq<a.rkrq
or(rkrq=a.rkrq and id<=a.id)
),4)
from rk_bak a
go--显示处理结果
select * from rk_bak
go--删除测试
drop table rk_bak/*--测试结果
id rkrq rkdh
----------- ------------------------------------------------------ ----------
1 1933-04-22 00:00:00.000 RK33040001
2 1933-04-22 00:00:00.000 RK33040002
3 2000-01-01 00:00:00.000 RK00010001
4 2001-04-03 00:00:00.000 RK01040001
5 2001-04-03 00:00:00.000 RK01040002
6 2001-04-03 00:00:00.000 RK01040003
7 2001-04-04 00:00:00.000 RK01040004
8 2001-04-04 00:00:00.000 RK01040005
9 2001-04-04 00:00:00.000 RK01040006
10 2001-04-04 00:00:00.000 RK01040007
11 2001-04-04 00:00:00.000 RK01040008
12 2001-04-04 00:00:00.000 RK01040009
13 2001-04-04 00:00:00.000 RK01040010
14 2001-04-04 00:00:00.000 RK01040011
15 2001-04-05 00:00:00.000 RK01040012
16 2001-04-05 00:00:00.000 RK01040013
17 2001-04-05 00:00:00.000 RK01040014
18 2001-04-05 00:00:00.000 RK01040015
19 2001-04-06 00:00:00.000 RK01040016(所影响的行数为 19 行)--*/
update rk_bak set rkdh='RK'+CONVERT(char(4),rkrq,12)
+right(10000+(
select count(*) from rk_bak
where datediff(month,rkrq,a.rkrq)=0 and rkrq<a.rkrq
or(rkrq=a.rkrq and id<=a.id)
),4)
from rk_bak a
那效率就低.你在rkrq上建立聚集索引,然后用下面这种方法吧:
declare @rq datetime,@i int
update rk_bak set @i=case datediff(month,rkrq,@rq) when 0 then @i+1 else 10001 end
,rkdh='RK'+CONVERT(char(4),rkrq,12)+right(@i,4)
,@rq=rkrq
问题解决了,顺便说两句,我已经奔四十的人了,可是对程序情有独钟,我虽然是学计算机专业的,但是一直没有做很系统的程序,现在也是业余时间搞点小程序,人总是要上进的,以前我听说过了三十岁就不能当程序员了,所以一直没有好好静下心来,可现在功利心没有了,只是想学一点算一点,就当是爱好了!
这里我常来, zjcxc(邹建) 的大名我常看到,以前未曾得到指教,这是我的第一问题,可能对于您来讲很简单,但对我来说,帮我解决了一个不小的问题,虽然我能利用别的方法写程序解决,但都是需要很长的时间,都是些笨的方法,我对sql只知道了一点点,找了好多贴子,就是没找到解决的方法,今天晚上解决,非常感谢!
同时我也想说,可能这些问题对你们高手没什么的,但对我这种门外汉来说,不知道有多高兴呢!我由衷的说一句,这里的风气真好,zjcxc(邹建)你是好样的!