declare @t table( id varchar(20),name varchar(20)) insert into @t select '0102' , 'pl' union all select '0103' , 'po' union all select '0102' , 'zzl' union all select '0102' , 'zl' union all select '0103' , 'kk' select * from @t select id=id+right('0000'+convert(varchar,(select isnull(count(*),0)+1 from @t where id=a.id and name<a.name)),4),name from @t a order by id /* 结果 id name -------------------- 01020001 pl 01020002 zl 01020003 zzl 01030001 kk 01030002 po*/
如果要按照原来的排序,可以取消后边的order by id
declare @tb table ( ID varchar(10), Name varchar(10), Re varchar(10) default '' ) insert @tb(ID,name) select '0102','pl' union all select '0103','po' union all select '0102','zzl' union all select '0102','zl' union all select '0103','kk' --测试 select [num]=identity(int,1,1),* into # from @tb select [ID]=ID+right(convert(varchar,(10000+(select count(1) from # where ID=t.ID and num<=t.num))),4), Name, Re from # t order by num drop table #--结果 /* ID Name Re ------------------ ---------- ---------- 01020001 pl 01030001 po 01020002 zzl 01020003 zl 01030002 kk (所影响的行数为 5 行) */
select [num]=identity(int,1,1),* into # from @tb select [ID]=ID+right(convert(varchar,(10000+(select count(1) from # where ID=t.ID and num<=t.num))),4), Name, Re from # t order by num能解释一下这段语句吗,谢谢
insert into @t
select '0102' , 'pl'
union all select '0103' , 'po'
union all select '0102' , 'zzl'
union all select '0102' , 'zl'
union all select '0103' , 'kk'
select * from @t
select id=id+right('0000'+convert(varchar,(select isnull(count(*),0)+1 from @t where id=a.id and name<a.name)),4),name
from @t a
order by id
/*
结果
id name
--------------------
01020001 pl
01020002 zl
01020003 zzl
01030001 kk
01030002 po*/
(
ID varchar(10),
Name varchar(10),
Re varchar(10) default ''
)
insert @tb(ID,name)
select '0102','pl' union all
select '0103','po' union all
select '0102','zzl' union all
select '0102','zl' union all
select '0103','kk' --测试
select [num]=identity(int,1,1),* into # from @tb
select [ID]=ID+right(convert(varchar,(10000+(select count(1) from # where ID=t.ID and num<=t.num))),4),
Name,
Re
from # t
order by num
drop table #--结果
/*
ID Name Re
------------------ ---------- ----------
01020001 pl
01030001 po
01020002 zzl
01020003 zl
01030002 kk (所影响的行数为 5 行)
*/
select [ID]=ID+right(convert(varchar,(10000+(select count(1) from # where ID=t.ID and num<=t.num))),4),
Name,
Re
from # t
order by num能解释一下这段语句吗,谢谢