• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

高分求助(不唯一的字段改成唯一)

Myyokel 2003-05-07 12:15:21
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
...全文
51 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
joygxd 2003-05-07
up
回复
CrazyFor 2003-05-07
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
回复
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
回复
qiuyang_wang 2003-05-07
帮你想想
回复
qiuyang_wang 2003-05-07
不好意思,我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>
回复
yoki 2003-05-07
都是高手啊,大力的方法妙
我只有学习的份了
回复
firetoucher 2003-05-07
楼上大哥用的都是临时表,你也可以用游标。
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

回复
CrazyFor 2003-05-07
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','达到','达到')

回复
pengdali 2003-05-07
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
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-07 12:15
社区公告
暂无公告