如何在没有临时表的SQL查询中为组添加序列号

Question

我在SQL2008中创建了一个复杂的搜索查询，该查询返回按组排序的数据，并且查询本身具有分页和排序功能，但是与其返回基于分页选项的一定数量的记录，还需要返回一个设置组数（因此记录数将有所不同）。

我目前正在通过使用临时表来执行此操作（第一个临时表创建将在搜索中选择的组的列表，然后为它们编号…第二个查询将此表连接到实际表搜索…因此，它最终会运行搜索查询两次）。

我正在寻找一种使用SQL 2008中的一些新功能（不需要使用临时表）的更有效的方法。

如果我能以这样的格式获取数据，那就可以了…

Record  Group     GroupSequence
-------|---------|--------------
1       Chickens  1
2       Chickens  1
3       Cows      2
4       Horses    3
5       Horses    3
6       Horses    3

关于如何在不使用临时表的情况下在SQL 2008中通过单个查询完成此操作的任何想法？

Answer 1

样本数据

create table sometable([group] varchar(10), id int, somedata int)
insert sometable select 'Horses', 9, 11
insert sometable select 'chickens', 19, 121
insert sometable select 'Horses', 29, 123
insert sometable select 'chickens', 49, 124
insert sometable select 'Cows', 98, 1
insert sometable select 'Horses', 99, 2

Query

select
    Record = ROW_NUMBER() over (order by [Group], id),
    [Group],
    GroupSequence = DENSE_RANK() over (order by [Group])
from sometable

Output

Record               Group      GroupSequence
-------------------- ---------- --------------------
1                    chickens   1
2                    chickens   1
3                    Cows       2
4                    Horses     3
5                    Horses     3
6                    Horses     3