如何在没有临时表的SQL查询中为组添加序列号 [英] How to add sequence number for groups in a SQL query without temp tables

问题描述

我已经在SQL 2008中创建了一个复杂的搜索查询，该查询返回按组排序的数据，并且查询本身具有分页和排序功能，但与其根据分页选项返回一定数量的记录，还需要返回一定数量的组(因此记录数会有所不同).

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).

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

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).

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

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).

如果我能以这种格式获取数据，那就可以了...

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

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

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

推荐答案

样本数据

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

这篇关于如何在没有临时表的SQL查询中为组添加序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！