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

I've created a complex search query in SQL 2008 that returns data sorted by groups, and the query itself has paging and sorting functions in it, but rather than returning a set number of records based on the paging options, it needs to return a set number of groups (so the number of records will vary).

我在SQL 2008中创建了一个复杂的搜索查询,它返回按组排序的数据,查询本身也有分页和排序功能,但它不是根据分页选项返回一定数量的记录,而是需要返回一个设定组数(因此记录数会有所不同)。

I'm currently doing this through the use of Temp Tables (the first temp table creates a list of the Groups that will be selected as part of the search, and then numbers them... and the second query joins this table to the actual search... so, it ends up running the search query twice).

我目前通过使用临时表来执行此操作(第一个临时表创建将作为搜索的一部分选择的组的列表,然后对它们进行编号...并且第二个查询将此表连接到实际搜索...所以,它最终运行搜索查询两次)。

What I'm looking for is a more efficient way to do this using some of the new functions in SQL 2008 (which wouldn't require the use of temp tables).

我正在寻找的是使用SQL 2008中的一些新函数(不需要使用临时表)来实现此目的的更有效方法。

If I can get the data in a format like this, I'd be set...

如果我能以这样的格式获取数据,我会被设置......

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

Any ideas on how to accomplish this with a single query in SQL 2008, without using temp tables?

有关如何使用SQL 2008中的单个查询完成此任务的任何想法,而不使用临时表?

2 个解决方案

#1

Sample data

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

#2

Without more details about the tables you have, I'd say look into CTE queries and the row_number function... something along the lines of:

如果没有关于你所拥有的表的更多细节,我会说看一下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

#1