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

Question

我在 SQL 2008 中创建了一个复杂的搜索查询，它返回按组排序的数据，并且该查询本身具有分页和排序功能，但它不是根据分页选项返回一组记录，而是需要返回一个设置组数（因此记录数会有所不同）。

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

我正在寻找一种更有效的方法，使用 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

查询

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

输出

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

Answer 2

如果没有有关您拥有的表的更多详细信息，我建议您查看 CTE 查询和 row_number 函数...大致如下：

;with groups as (
  select top 10 name, row_number() over(order by name) 'sequence'
  from table1
  group by name
  order by name
)
select row_number() over(order by g.name) 'Record',
  g.name 'GroupName',
  g.sequence 'GroupSequence'
from groups