如何将查询结果分组编号

kasdf123 2008-05-15 06:08:47

如有表,key1和key2为联合主键:
key1 key2 content1 content2 content3
A B ... ... ...
C B ... ... ...
A B ... ... ...
A B ... ... ...
D E ... ... ...
C B ... ... ...
------------------------------------------
查询结果为:

1 A B ... ... ...
2 A B ... ... ...
3 A B ... ... ...
1 C B ... ... ...
2 C B ... ... ...
1 D E ... ... ...

...全文

53 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

zzyyc 2008-05-15

打赏
举报

select IDENTITY(int,1,1) as id ,T.* into #b from #T t

select (select count(id) from #b where key1=b.key1 and id<=b.id) as id,b.key1,b.key2,b.content1,b.content2,b.content3 from #b b order by
(cast(key1 as varchar(10))+cast(key2 as varchar(10)))

1 A B ... ... ...
2 A B ... ... ...
3 A B ... ... ...
1 C B ... ... ...
2 C B ... ... ...
1 D E ... ... ...

zzyyc 2008-05-15

打赏
举报

没看清前面一列还有用。是行数合计。

kasdf123 2008-05-15

打赏
举报

3楼的兄弟你理解错了

zzyyc 2008-05-15

打赏
举报

select * from tab order by (cast(key1 as varchar(10))+cast(key2 as varchar(10)))

Limpire 2008-05-15

打赏
举报

--> 测试数据: #T

if object_id('tempdb.dbo.#T') is not null drop table #T

create table #T (key1 varchar(1),key2 varchar(1),content1 varchar(3),content2 varchar(3),content3 varchar(3))

insert into #T

select 'A','B','...','...','...' union all

select 'C','B','...','...','...' union all

select 'A','B','...','...','...' union all

select 'A','B','...','...','...' union all

select 'D','E','...','...','...' union all

select 'C','B','...','...','...'



--> 2005

select id=row_number()over(partition by key1 order by key1), * from #T



--> 2000

if object_id('tempdb.dbo.#') is not null drop table #

select id=identity(int,1,1),* into # from #T order by key1

select id1=(select count(1) from # where key1=t.key1 and id<=t.id),key1,key2,content1,content2,content3 from # as t



/*

id1         key1 key2 content1 content2 content3

----------- ---- ---- -------- -------- --------

1           A    B    ...      ...      ...

2           A    B    ...      ...      ...

3           A    B    ...      ...      ...

1           C    B    ...      ...      ...

2           C    B    ...      ...      ...

1           D    E    ...      ...      ...

*/

Limpire 2008-05-15

打赏
举报

--> 测试数据: #T

if object_id('tempdb.dbo.#T') is not null drop table #T

create table #T (key1 varchar(1),key2 varchar(1),content1 varchar(3),content2 varchar(3),content3 varchar(3))

insert into #T

select 'A','B','...','...','...' union all

select 'C','B','...','...','...' union all

select 'A','B','...','...','...' union all

select 'A','B','...','...','...' union all

select 'D','E','...','...','...' union all

select 'C','B','...','...','...'



select id=row_number()over(partition by key1 order by key1), * from #T

/*

id                   key1 key2 content1 content2 content3

-------------------- ---- ---- -------- -------- --------

1                    A    B    ...      ...      ...

2                    A    B    ...      ...      ...

3                    A    B    ...      ...      ...

1                    C    B    ...      ...      ...

2                    C    B    ...      ...      ...

1                    D    E    ...      ...      ...

*/