Microsoft SQL Server
Transact-SQL

Возвращение порядковых номеров строк в результирующем наборе

Опубликовано: 02 окт 02
Рейтинг:

Автор: fima
Прислал: Glory

Q.
Как организовать запрос к БД, чтобы он возвращал не только данные, но и порядковые номера строк в результирующем наборе ?

A.

Способов решить эту задачу несколько.
use pubs

set nocount on
if exists (select * from sysobjects where type = 'U' and  name = 'test')
begin
	drop table test
end
/* создание таблицы для примера */
create table test
(
	id_test int identity not null,
	string char (7),
	constraint pk_test primary key (id_test)
)
/* установка флага для занесения с определенными ид. */
set identity_insert test on
/* занесение тестовых значений с произвольными ид. */
insert into test (id_test, string)
values (1,'string1')
insert into test (id_test, string)
values (4,'string2')
insert into test (id_test, string)
values (12,'string3')
insert into test (id_test, string)
values (17,'string4')
insert into test (id_test, string)
values (29,'string5')
insert into test (id_test, string)
values (31,'string6')
insert into test (id_test, string)
values (42,'string7')
insert into test (id_test, string)
values (45,'string8')
insert into test (id_test, string)
values (61,'string9')
/* отмена установки флага для занесения с определенными ид. */
set identity_insert test off
go
/* способ №1, создание проекции. */
if exists (select * from sysobjects where type = 'V' and name = 'ranked_table')
begin
	drop view ranked_table
end
go
create view ranked_table (rank, id_test, sting)
as
	select (
			select count(*) 
			from test as test_2
			where test_2.id_test <= test_1.id_test
		) as rank,
		test_1.id_test,
		test_1.string
	from test as test_1
go
select * 
from ranked_table
order by rank
go
/* способ №2 стандартный SQL */
select count (test_2.id_test) as rank, test_1.id_test, test_1.string
from test as test_1 inner join test as test_2 on
	test_1.id_test >= test_2.id_test
group by test_1.id_test, test_1.string
order by rank
go
/* способ №3 стандартный SQL */
select test_3.rank, test_3.id_test, test_3.string
from (select test_1.id_test,
		test_1.string,
		(select count(*) 
			from test as test_2
			where test_2.id_test <= test_1.id_test
		) as rank
	from test as test_1) as test_3
order by rank
go
/* способ №4, временная таблица с полем identity */
create table #rank_table
(
	rank int identity not null,
	id_test int null,
	string char (7),
	constraint pk_test primary key (rank)
)
go
insert into #rank_table (id_test, string)
select id_test, string
from test
order by id_test
select * from #rank_table
go
/* способ №5, переменная типа table с полем identity */
declare @rank_table table
(
	rank int identity not null,
	id_test int null,
	string char (7)
)
insert into @rank_table (id_test, string)
select id_test, string
from test
order by id_test
select * from @rank_table
go
/* способ №6, курсор */
declare @rank int,
	@id_test int,
	@string char (7)
declare rank_cursor cursor
for select id_test, string
	from test
	order by id_test
open rank_cursor
fetch next from rank_cursor into @id_test, @string
set @rank = 1
while (@@fetch_status <> -1)
begin
	select @rank, @id_test, @string
	set @rank = @rank + 1
	fetch next from rank_cursor into @id_test, @string
end
close rank_cursor
deallocate rank_cursor
/* результат всех примеров
rank        id_test     string  
----------- ----------- ------- 
1           1           string1
2           4           string2
3           12          string3
4           17          string4
5           29          string5
6           31          string6
7           42          string7
8           45          string8
9           61          string9
 */


Соответственно нужно выбрать подходящий Вам.
Обратите внимание что пример №3 входит состовляющей частью в пример №1. Поэтому на примере №2 тоже можно построить проекцию. На мой взгляд, проекция на примере №2 наиболее оптимальна, вместе с примером №5. Выбор из №2 и №5 зависит от количества данных и пр.

Пример №5 будет работать только на SQL 2000. На SQL 2000 лучше использовать переменые типа table вместо временных таблиц, если это возможно.

Пример №6 на мой взгляд, наиболее неудачный и я привел его для общей эрудиции. Вроде привел всё что вспомнил. Если кто знает еще способы пишите.

Комментарии


  • 10 мая 2011, 13:09 Mikhail Tchervonenko

    для версий до 2005
    select rank=count(*), a1.au_lname, a1.au_fname
    from authors a1, authors a2
    where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
    group by a1.au_lname, a1.au_fname
    order by rank

  • 10 мая 2011, 13:08 Mikhail Tchervonenko

    Начиная с 2005 сервера
    select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
    from authors a
    order by rank

  • 21 октября 2010, 14:05 Даниил Пакоv

    Спасибо

  • есть ещё прикольный способ для sql2000 и выше.
    Взято из методы "23 золотых совета по работе с MS SQL Serve" Шкрыль Андрея www.vr-online.ru:

    declare i int
    set i=0

    select 0 as id, Name, Comm
    into #temp
    from MyTable

    update #temp set @i=id=@i+1

    select * from #temp

    drop table #temp

  • http://support.microsoft.com/kb/186133/ru

  • Если не важен порядок нумерации, то
    row_number()over(order by current_timestamp)
    - получается полный аналог ораклового rownum

    При вставке в таблицу можно использовать identity(1,1)

  • познавательно, спасибо. обычно использовал способы, представленные номерами 4 и 5

  • 14 июня 2007, 07:25 xm?!_(mo3roBa9_akTuBHocTb)

    Вот как я сделал, нужно было из таблицы выбрать все несовпадающие имена и добавить к ним счётчик:
    SELECT T2.Name, ROW_NUMBER() OVER(ORDER BY T2.Name)
    FROM
    (SELECT DISTINCT(T1.Name)
    FROM table1 AS T1
    ) AS T2
    Скорее всего это одна из модификаций одного из перечисленных выше способов.

  • все это конечно же бред :( , пока нет готовой функции.. которую в 2005-м наконец ввели.

  • Спасибо в 2005 что-нибудь добавили?

  • >Shurgenz
    >Проблема снимется, если перейти на 2005
    предлагаю дополнить топик вариантами для 2005

  • На 300000 записей оказалась наиболее эффективной вариация способа №4

  • Проблема снимется, если перейти на 2005

  • Способ 2 пробовал. Из таблички в 15 000 записей доставал 3 поля - вычисляемый номер строки, наименование, айдишник. В итоге - полторы минуты на выполнение - не очень утешает (((

  • Даааааааааааа, давно искал такую статейку. Спа автору :) за инфу

  • ... хотя список не полный и вариантов можно придумать еще несколько, вариант 5 считаю самым простым и оптимальным (хотя, возможно, и не самым быстрым)

  • Классная подборка

  • Надеюсь в Юконе будет как Оракле.

  • спасибо большое

  • Спасибо, очень долго искал

  • Я и не знал что может быть столько способов!!!



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Возвращение порядковых номеров строк в результирующем наборе