select identity(int,1,1) as id, * into #t from yourtable
declare @id int
declare @max_dept_num varchar(5)
set @id = 1
select @max_dept_num = max(dept_num) from #t
while exists (select * from yourtable as A where exists
(select * from yourtable where dept_num = A.dept_num and id < A.id)
begin
select top 1 @id = id from yourtable as A where exists
(select * from yourtable where dept_num = A.dept_num and id < A.id)
set @max_dept_num
= right(cast((cast(@max_dept_num as int) + 100001) as varchar(6)), 5)
update #t set dept_num = @max_dept_num
end
select cp_num, dept_num, name, chief from #t
declare @id int
declare @max_dept_num varchar(5)
set @id = 1
select @max_dept_num = max(dept_num) from #t
while exists (select * from yourtable as A where exists
(select * from yourtable where dept_num = A.dept_num and id < A.id)
begin
select top 1 @id = id from yourtable as A where exists
(select * from yourtable where dept_num = A.dept_num and id < A.id)
set @max_dept_num
= right(cast((cast(@max_dept_num as int) + 100001) as varchar(6)), 5)
update #t set dept_num = @max_dept_num
end
select cp_num, dept_num, name, chief from #t
declare @i int,@maxId int
set @i=1
set @maxId = select max(id) from #t
while @i<= @maxId
begin
set rowcount 1
update #t set dept_num = right('00000',cast(max(a.dept_num) as int)+1,5) from #t a where dept_num = A.dept_num and id >A.id
set @i=@i+1
end
select * from #t
insert #a values(3,'00001','人力资源部','胡匪')
insert #a values(4,'00001','研发部','关羽')
insert #a values(5,'00001','达到','达到')
insert #a values(4,'00002','服务部','张飞')
insert #a values(3,'00002','研发部','诸葛亮')declare @a varchar(100),@b varchar(100),@c varchar(100)
select @a=max(dept_num) from #a
update #a set
@a=case when @b=dept_num then @a+1 else @a end,
@c=case when @b=dept_num then right('00000'+cast(@a as varchar(5)),5) else dept_num end,
@b=dept_num,
dept_num=@cselect * from #a
go
drop table #a
insert #a values(3,'00001','人力资源部','胡匪')
insert #a values(4,'00001','研发部','关羽')
insert #a values(4,'00002','服务部','张飞')
insert #a values(3,'00002','研发部','诸葛亮')
insert #a values(5,'00001','达到','达到')
declare @max_dept_num int
select @max_dept_num = max(dept_num) from tDECLARE E_Cursor CURSOR FOR
SELECT cp_num, dept_num FROM t
OPEN E_Cursor
FETCH NEXT FROM E_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
update t
set dept_num = @max + 1
where cp_num=E_Cursor.cp_num and dept_num = E_Cursor.dept_num
select @max = @max +1
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
ORACLE环境:
SQL>create table ab (aa varchar2(10),bb varchar2(10));SQL>desc ab
Name Null? Type
----------------------------------------- -------- ----------------------------
AA VARCHAR2(10)
BB VARCHAR2(10)SQL>begin
2 insert into ab values ('1','00001');
3 insert into ab values ('2','00001');
4 insert into ab values ('3','00001');
5 insert into ab values ('4','00002');
6 insert into ab values ('5','00002');
7 commit;
8 end;
9 /PL/SQL procedure successfully completed.SQL>select * from ab;AA BB
---------- ----------
1 00001
2 00001
3 00001
4 00002
5 00002SQL> update ab set bb = to_char((select max(to_number(bb)) from ab) + rownum,'00000')
2 where bb in (select bb from ab group by bb having count(bb) > 1)
3 and aa || bb not in (select min(aa)||bb from ab group by bb having count(bb) > 1);3 rows updated.SQL> commit;Commit complete.SQL> select * from ab;AA BB
---------- ----------
1 00001
2 00003
3 00004
4 00002
5 00005SQL>