分组中加顺序号
表A
bm xm
ba xxx
ba yyy
ba fff
bb dfdf
bb fd
bb fdf
要求按编码分组加上顺序号
结果如下
序号 bm xm
ba001 ba xxx
ba002 ba yyy
ba003 ba fff
bb001 bb dfdf
bb002 bb fd
bb003 bb fdf
问题点数：20、回复次数：5Top
1 楼happyflystone（无枪的狙击手）回复于 2006-03-07 16:22:57 得分 0
select bm+right('0000'+cast((select count(1) from table where bm = a.bm and xm >a.xm) as varchar(3)),3),bm,xm
from table aTop
2 楼happyflystone（无枪的狙击手）回复于 2006-03-07 16:26:08 得分 17
declare @t table(bm char(2), xm varchar(4))
insert @t
select 'ba','xxx' union all
select 'ba','yyy' union all
select 'ba','fff' union all
select 'bb','dfdf' union all
select 'bb','fd' union all
select 'bb','fdf'
select
bm+right('0000'+cast((select count(1)+1 from @t where bm = a.bm and xm >a.xm) as varchar(3)),3) as bbm,
bm,xm
from @t a
order by bbm
/*
bbm bm xm
-------- ---- ----
ba001 ba yyy
ba002 ba xxx
ba003 ba fff
bb001 bb fdf
bb002 bb fd
bb003 bb dfdf
*/Top
3 楼zhaoanle（zhao）回复于 2006-03-07 16:43:01 得分 1
select
a.bm+right('000'+cast((select count(1) from 表A where xm>=a.xm and bm=a.bm)as varchar(10)),3) as bm,
bm,xm
from 表A aTop
4 楼mislrb（上班看看早报，上上CSDN，下班看看电影）回复于 2006-03-07 16:47:29 得分 1
declare @t table(bm varchar(2), xm varchar(4))
insert @t
select 'ba','xxx' union all
select 'ba','yyy' union all
select 'ba','fff' union all
select 'bb','dfdf' union all
select 'bb','fd' union all
select 'bb','fdf'
select id=identity(int,1,1),* into #t from @t
select id=rtrim(ltrim(bm))+right('000'+cast(id+1-(select min(id) from #t where bm=t.bm) as varchar(5)),3),t.bm,t.xm
from #t t
drop table #t
Top
5 楼vovo2000（没人要的猫）回复于 2006-03-07 16:48:32 得分 1
declare @t table(bm char(2), xm varchar(4))
insert @t
select 'ba','xxx' union all
select 'ba','yyy' union all
select 'ba','fff' union all
select 'bb','dfdf' union all
select 'bb','fd' union all
select 'bb','fdf'
select
T1.bm +'00' +convert(varchar(3),count(1)) ,
T1.xm
from
@t T1 join @t T2
on T1.bm = T2.bm and T1.xm >= T2.xm
group by
T1.bm,T1.xm
--------------
ba xxx
ba yyy
ba fff
bb dfdf
bb fd
bb fdf
------------
bb001 dfdf
bb002 fd
bb003 fdf
ba001 fff
ba002 xxx
ba003 yyy
Top
