Опубликовано: 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 на мой взгляд, наиболее неудачный и я привел его для общей эрудиции. Вроде привел всё что вспомнил. Если кто знает еще способы пишите.
Комментарии
для версий до 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
Начиная с 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
Спасибо
есть ещё прикольный способ для 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
Вот как я сделал, нужно было из таблицы выбрать все несовпадающие имена и добавить к ним счётчик:
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 считаю самым простым и оптимальным (хотя, возможно, и не самым быстрым)
Классная подборка
Надеюсь в Юконе будет как Оракле.
спасибо большое
Спасибо, очень долго искал
Я и не знал что может быть столько способов!!!