高分求助（不唯一的字段改成唯一）

[Myyokel]

cp_num dept_num name chief
------ -------- ------- -----
3 00001 人力资源部 胡匪
4 00001 研发部 关羽
5 00001 达到 达到
4 00002 服务部 张飞
3 00002 研发部 诸葛亮

转换成：
cp_num dept_num name chief
------ -------- ------- -----
3 00001 人力资源部 胡匪
4 00003 研发部 关羽 （将dept_num改成当前最大的+1）00002+1
5 00004 达到 达到 （将dept_num改成当前最大的+1）00003+1
4 00002 服务部 张飞
3 00005 研发部 诸葛亮 （将dept_num改成当前最大的+1）00004+1

[pengdali]

create table #a (cp_num varchar(100),dept_num varchar(100),name varchar(100),chief varchar(100))
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=@c

select * from #a
go
drop table #a

[CrazyFor]

pengdali(大力)提供的非常精辟,不过你必需保证所有相同的dept_num号必需连续出现,要不然后面的分开出现的dept_num 号就不会被更新.

TRY:下面的数据中的最后一条就不会被更新.

create table #a (cp_num varchar(100),dept_num varchar(100),name varchar(100),chief varchar(100))
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','达到','达到')

[firetoucher]

楼上大哥用的都是临时表，你也可以用游标。
declare @max_dept_num int
select @max_dept_num = max(dept_num) from t

DECLARE 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

[yoki]

都是高手啊，大力的方法妙
我只有学习的份了

[qiuyang_wang]

不好意思，我SQL SERVER学的不是很好，所以只能用ORACLE下的方法给你解决了，但原可以给你一点提示。
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 00002

SQL> 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 00005

SQL>

[qiuyang_wang]

帮你想想

[愉快的登山者]

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

[CrazyFor]

TRY:

select identity(int,1,1) as id, * into #t from yourtable
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

[joygxd]

up