如果用a,b,c不好弄,如果用1,2,3可以借助2005中的row_number() over () 来解决
-->生成测试数据
declare @spkfk table([spmch] nvarchar(11),[shpgg] nvarchar(17),[shpchd] nvarchar(10),[count] int) Insert @spkfk select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 Select row_number() over(partition by [spmch],[shpchd],[shpgg],[count] order by [spmch],[shpgg],[shpchd],[count]) as rowm, [spmch], case row_number() over(partition by [spmch],[shpchd],[shpgg],[count] order by [spmch],[shpgg],[shpchd],[count]) when 1 then [shpgg] else [shpgg] + char(63 + row_number() over(partition by [spmch],[shpchd],[shpgg],[count] order by [spmch],[shpgg],[shpchd],[count])) end as [shpgg], [shpchd], [count] from @spkfk /*(8 row(s) affected) rowm spmch shpgg shpchd count -------------------- ----------- ------------------ ---------- ----------- 1 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40 山西公司 1 2 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40A 山西公司 1 3 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40B 山西公司 1 4 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40C 山西公司 1 1 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40 山西三晋药业有限公司 1 2 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40A 山西三晋药业有限公司 1 3 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40B 山西三晋药业有限公司 1 4 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40C 山西三晋药业有限公司 1(8 row(s) affected)*/ declare @mchk table([dwmch] nvarchar(8),[count] int) Insert @mchk select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗院',1 union all select N'沙岗院',1 union all select N'沙岗院',1 union all select N'沙岗院',1 union all select N'沙岗镇卫生院',1 union all select N'沙岗镇卫生院',1 union all select N'沙岗镇卫生院',1 union all select N'沙岗镇卫生院',1 --Select * from @mchkSelect row_number() over(partition by [dwmch],[count] order by [dwmch],[count]) as rowm, [dwmch], case row_number() over(partition by [dwmch],[count] order by [dwmch],[count]) when 1 then [dwmch] else [dwmch] + char(63 + row_number() over(partition by [dwmch],[count] order by [dwmch],[count])) end as [dwmch], [count] from @mchk /* rowm dwmch dwmch count -------------------- -------- --------- ----------- 1 沙岗院 沙岗院 1 2 沙岗院 沙岗院A 1 3 沙岗院 沙岗院B 1 4 沙岗院 沙岗院C 1 1 沙岗镇卫生院 沙岗镇卫生院 1 2 沙岗镇卫生院 沙岗镇卫生院A 1 3 沙岗镇卫生院 沙岗镇卫生院B 1 4 沙岗镇卫生院 沙岗镇卫生院C 1 1 沙岗镇中心卫生院 沙岗镇中心卫生院 1 2 沙岗镇中心卫生院 沙岗镇中心卫生院A 1 3 沙岗镇中心卫生院 沙岗镇中心卫生院B 1 4 沙岗镇中心卫生院 沙岗镇中心卫生院C 1 5 沙岗镇中心卫生院 沙岗镇中心卫生院D 1 6 沙岗镇中心卫生院 沙岗镇中心卫生院E 1 */ PS:2000使用临时表, 2005使用Row_number() 弱弱地问一下,如果字母用完了用什么?
1.建一表: create table key(s varchar(8000),id int) 2.写个存储过程,传入字符串,返回字符串。 create proc gets(@s varchar(8000),@d varchar(8000) output) as begin declare @i int,@k int,@c varchar(100) select @d = @s,@c='' if not exists(select 1 from key where s = @s) begin insert key select @s,0 return end update key set id = id + 1 where s = @s select @i = id - 1 from key where s = @s while @i>0 begin select @c = char(ascii('a')+@i%26)+@c select @i = @i/26 end select @d = @d + @c return end 3.对需要处理的表使用游标逐行处理。
select @i = id - 1 from key where s = @s while @i>0 begin select @c = char(ascii('a')+@i%26)+@c =========》 select @i = id - 1 from key where s = @s while @i>0 begin select @i = @i - 1 select @c = char(ascii('a')+@i%26)+@c
还是错了: select @i = id - 1 from key where s = @s while @i>0 ====》 select @i = id - 1 from key where s = @s if @i = 0 select @d = @d+'a' while @i>0
--2000的处理方式,多栏位也是类似的原理.... declare @tb table(id int identity(1,1),[dwmch] nvarchar(8),[count] int) declare @mchk table([dwmch] nvarchar(8),[count] int) Insert @mchk select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗镇中心卫生院',1 union all select N'沙岗院',1 union all select N'沙岗院',1 union all select N'沙岗院',1 union all select N'沙岗院',1 union all select N'沙岗镇卫生院',1 union all select N'沙岗镇卫生院',1 union all select N'沙岗镇卫生院',1 union all select N'沙岗镇卫生院',1insert into @tb Select * from @mchkselect px=(select count(1)+1 from @tb where id <t.id and [dwmch]=t.[dwmch] and [count] =t.[count]),t.[dwmch],t.[count] from @tb t
请专家指点一下,我这哪里出现了问题(sqlserver 2000) -- -->生成测试数据 -- select *from spkfk -- drop table spkfk -- create table spkfk ([id] int identity(1,1),[spmch] nvarchar(11),[shpgg] nvarchar(100),[shpchd] nvarchar(10),[count] int) -- Insert spkfk -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all -- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1-------------------------- DECLARE @id int,@spmch nvarchar(11), @shpgg nvarchar(17),@shpchd nvarchar(10),@count int declare @jls int, @i int, @j int, @k int declare @char char(2)set @i=0declare s1 cursor for select spmch,shpgg,shpchd,count(*) count from spkfk group by spmch,shpgg,shpchd having count(*)>1 order by count(*) desc
OPEN s1 FETCH NEXT FROM s1 into @spmch,@shpgg,@shpchd,@count While @@fetch_status<>0 Begin FETCH NEXT FROM s1 into @spmch,@shpgg,@shpchd,@count SET @jls=@count declare s2 cursor for select id,spmch,shpgg,shpchd from spkfk where spmch=@spmch and shpgg=@shpgg and shpchd=@shpchd open s2 FETCH NEXT FROM s2 into @id,@spmch,@shpgg,@shpchd While @i<=@count BEGIN FETCH NEXT FROM s2 into @id,@spmch,@shpgg,@shpchd set @char= char(ascii( 'a')+@i) update spkfk set shpgg=shpgg+@char where id=@id and spmch=@spmch and shpgg=@shpgg and shpchd=@shpchd set @i=@i+1 END CLOSE s2 DEALLOCATE s2 END CLOSE s1 DEALLOCATE s1select *from spkfk
如果用a,b,c不好弄,如果用1,2,3可以借助2005中的row_number() over () 来解决
declare @spkfk table([spmch] nvarchar(11),[shpgg] nvarchar(17),[shpchd] nvarchar(10),[count] int)
Insert @spkfk
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all
select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1
Select row_number() over(partition by [spmch],[shpchd],[shpgg],[count] order by [spmch],[shpgg],[shpchd],[count]) as rowm,
[spmch],
case row_number() over(partition by [spmch],[shpchd],[shpgg],[count] order by [spmch],[shpgg],[shpchd],[count]) when 1 then [shpgg]
else
[shpgg] + char(63 + row_number() over(partition by [spmch],[shpchd],[shpgg],[count] order by [spmch],[shpgg],[shpchd],[count]))
end as [shpgg],
[shpchd],
[count]
from @spkfk
/*(8 row(s) affected)
rowm spmch shpgg shpchd count
-------------------- ----------- ------------------ ---------- -----------
1 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40 山西公司 1
2 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40A 山西公司 1
3 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40B 山西公司 1
4 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40C 山西公司 1
1 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40 山西三晋药业有限公司 1
2 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40A 山西三晋药业有限公司 1
3 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40B 山西三晋药业有限公司 1
4 咳必清(枸橼酸喷托维) 25mg*100s*10瓶*40C 山西三晋药业有限公司 1(8 row(s) affected)*/
declare @mchk table([dwmch] nvarchar(8),[count] int)
Insert @mchk
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗院',1 union all
select N'沙岗院',1 union all
select N'沙岗院',1 union all
select N'沙岗院',1 union all
select N'沙岗镇卫生院',1 union all
select N'沙岗镇卫生院',1 union all
select N'沙岗镇卫生院',1 union all
select N'沙岗镇卫生院',1
--Select * from @mchkSelect row_number() over(partition by [dwmch],[count] order by [dwmch],[count]) as rowm,
[dwmch],
case row_number() over(partition by [dwmch],[count] order by [dwmch],[count]) when 1 then [dwmch]
else
[dwmch] + char(63 + row_number() over(partition by [dwmch],[count] order by [dwmch],[count]))
end as [dwmch],
[count]
from @mchk
/*
rowm dwmch dwmch count
-------------------- -------- --------- -----------
1 沙岗院 沙岗院 1
2 沙岗院 沙岗院A 1
3 沙岗院 沙岗院B 1
4 沙岗院 沙岗院C 1
1 沙岗镇卫生院 沙岗镇卫生院 1
2 沙岗镇卫生院 沙岗镇卫生院A 1
3 沙岗镇卫生院 沙岗镇卫生院B 1
4 沙岗镇卫生院 沙岗镇卫生院C 1
1 沙岗镇中心卫生院 沙岗镇中心卫生院 1
2 沙岗镇中心卫生院 沙岗镇中心卫生院A 1
3 沙岗镇中心卫生院 沙岗镇中心卫生院B 1
4 沙岗镇中心卫生院 沙岗镇中心卫生院C 1
5 沙岗镇中心卫生院 沙岗镇中心卫生院D 1
6 沙岗镇中心卫生院 沙岗镇中心卫生院E 1
*/
PS:2000使用临时表,
2005使用Row_number()
弱弱地问一下,如果字母用完了用什么?
create table key(s varchar(8000),id int)
2.写个存储过程,传入字符串,返回字符串。
create proc gets(@s varchar(8000),@d varchar(8000) output)
as
begin
declare @i int,@k int,@c varchar(100)
select @d = @s,@c=''
if not exists(select 1 from key where s = @s)
begin
insert key select @s,0
return
end
update key set id = id + 1 where s = @s
select @i = id - 1 from key where s = @s
while @i>0
begin
select @c = char(ascii('a')+@i%26)+@c
select @i = @i/26
end
select @d = @d + @c
return
end
3.对需要处理的表使用游标逐行处理。
while @i>0
begin
select @c = char(ascii('a')+@i%26)+@c
=========》
select @i = id
- 1from key where s = @swhile @i>0
begin
select @i = @i - 1
select @c = char(ascii('a')+@i%26)+@c
select @i = id - 1 from key where s = @s
while @i>0
====》
select @i = id - 1 from key where s = @s
if @i = 0 select @d = @d+'a'
while @i>0
declare @tb table(id int identity(1,1),[dwmch] nvarchar(8),[count] int)
declare @mchk table([dwmch] nvarchar(8),[count] int)
Insert @mchk
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗镇中心卫生院',1 union all
select N'沙岗院',1 union all
select N'沙岗院',1 union all
select N'沙岗院',1 union all
select N'沙岗院',1 union all
select N'沙岗镇卫生院',1 union all
select N'沙岗镇卫生院',1 union all
select N'沙岗镇卫生院',1 union all
select N'沙岗镇卫生院',1insert into @tb Select * from @mchkselect px=(select count(1)+1 from @tb where id <t.id and [dwmch]=t.[dwmch] and [count] =t.[count]),t.[dwmch],t.[count]
from @tb t
/*
px dwmch count
----------- -------- -----------
1 沙岗镇中心卫生院 1
2 沙岗镇中心卫生院 1
3 沙岗镇中心卫生院 1
4 沙岗镇中心卫生院 1
5 沙岗镇中心卫生院 1
6 沙岗镇中心卫生院 1
1 沙岗院 1
2 沙岗院 1
3 沙岗院 1
4 沙岗院 1
1 沙岗镇卫生院 1
2 沙岗镇卫生院 1
3 沙岗镇卫生院 1
4 沙岗镇卫生院 1(14 row(s) affected)
*/
-- -->生成测试数据
-- select *from spkfk
-- drop table spkfk
-- create table spkfk ([id] int identity(1,1),[spmch] nvarchar(11),[shpgg] nvarchar(100),[shpchd] nvarchar(10),[count] int)
-- Insert spkfk
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西三晋药业有限公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1 union all
-- select N'咳必清(枸橼酸喷托维)',N'25mg*100s*10瓶*40',N'山西公司',1--------------------------
DECLARE @id int,@spmch nvarchar(11), @shpgg nvarchar(17),@shpchd nvarchar(10),@count int
declare @jls int, @i int, @j int, @k int
declare @char char(2)set @i=0declare s1 cursor
for
select spmch,shpgg,shpchd,count(*) count
from spkfk
group by spmch,shpgg,shpchd
having count(*)>1 order by count(*) desc
OPEN s1
FETCH NEXT FROM s1 into @spmch,@shpgg,@shpchd,@count
While @@fetch_status<>0
Begin
FETCH NEXT FROM s1 into @spmch,@shpgg,@shpchd,@count
SET @jls=@count
declare s2 cursor
for select id,spmch,shpgg,shpchd from spkfk where spmch=@spmch and shpgg=@shpgg and shpchd=@shpchd
open s2
FETCH NEXT FROM s2 into @id,@spmch,@shpgg,@shpchd
While @i<=@count
BEGIN
FETCH NEXT FROM s2 into @id,@spmch,@shpgg,@shpchd
set @char= char(ascii( 'a')+@i)
update spkfk set shpgg=shpgg+@char where id=@id and spmch=@spmch and shpgg=@shpgg and shpchd=@shpchd
set @i=@i+1
END
CLOSE s2
DEALLOCATE s2
END
CLOSE s1
DEALLOCATE s1select *from spkfk