if object_id('aa') is not null drop table aa go select '一' as kind_name, 'aa' as line_name into aa union select '一', 'bb' union select '一', 'cc' union select '二', 'aa' union select '二', 'bb' go select * from aa order by kind_name desc /* 一 aa 一 bb 一 cc 二 aa 二 bb */select isnull(cast(findex as varchar(4)), '') as 序号, 名称 from (select kind_name, (select count(*) from aa where kind_name = a.kind_name and line_name <= a.line_name) as findex, line_name as 名称 from aa a union select kind_name, null, kind_name from aa group by kind_name) b order by kind_name desc, findex /* 一 1 aa 2 bb 3 cc 二 1 aa 2 bb */ drop table aa
declare @aa table(kind_name varchar(5),line_name varchar(5)) insert @aa select '一','aa' union all select '一','bb' union all select '一','cc' union all select '二','aa' union all select '二','bb' select 序号,名称 from (select 序号=(select count(1) from @aa where kind_name=t.kind_name and line_name<=t.line_name), 名称=line_name, id=kind_name from @aa t union allselect distinct null,kind_name,kind_name from @aa )t order by t.id desc,t.序号
go
select '一' as kind_name, 'aa' as line_name
into aa
union select '一', 'bb'
union select '一', 'cc'
union select '二', 'aa'
union select '二', 'bb'
go
select * from aa order by kind_name desc
/*
一 aa
一 bb
一 cc
二 aa
二 bb
*/select isnull(cast(findex as varchar(4)), '') as 序号, 名称
from (select kind_name, (select count(*) from aa where kind_name = a.kind_name and line_name <= a.line_name) as findex, line_name as 名称
from aa a
union
select kind_name, null, kind_name
from aa
group by kind_name) b
order by kind_name desc, findex
/*
一
1 aa
2 bb
3 cc
二
1 aa
2 bb
*/
drop table aa
declare @aa table(kind_name varchar(5),line_name varchar(5))
insert @aa
select '一','aa' union all
select '一','bb' union all
select '一','cc' union all
select '二','aa' union all
select '二','bb'
select 序号,名称 from
(select 序号=(select count(1) from @aa where kind_name=t.kind_name and line_name<=t.line_name),
名称=line_name,
id=kind_name
from @aa t union allselect distinct null,kind_name,kind_name from @aa )t
order by t.id desc,t.序号