Как определить положение строки в наборе результатов sql?

у меня есть sql-запрос:

select id, name from table order by name

результат выглядит так:

52 arnold 
33 berta 
34 chris 
47 doris
52 emil

для данного id = 47, как я могу определить позицию в наборе результатов? результат должен быть 4, потому что:

52 arnold
33 berta
34 chris

находятся перед (47, doris), а id=41 находится на 4-й позиции в наборе результатов.

Как это сделать в SQL? Как в HQL? В примере с разбивкой на страницы мне нужно выполнить 2 оператора или есть решение, в котором я могу получить именно то окно, которое содержит строку с id = 47?

постгрескл и джава


sql count
person Chris    schedule 03.07.2009    source источник

Ответы (5)


arrow_upward
15
arrow_downward

Предыдущие посты верны. Используйте ROW_NUMBER при использовании Microsoft SQL Server 2005 или более поздней версии.

Однако ваши теги не указывают, что вы используете MSSQL, поэтому вот решение, которое должно работать в большинстве реализаций СУБД. По сути, используйте коррелированный подзапрос, чтобы определить количество строк в том же наборе, которые меньше, чем текущая строка, на основе значений предложения ORDER внешнего запроса. Что-то вроде этого:

SELECT      T1.id,
            T1.[name],
            (SELECT COUNT(*) 
             FROM table T2 
             WHERE T2.[name] < T1.[name]) + 1 AS rowpos
FROM        table T1
ORDER BY    T1.[name]
person Bob Mc    schedule 03.07.2009
comment
Я думаю, это зависит от того, что вы хотите использовать, чтобы разорвать галстук. Вы можете добавить дополнительные критерии к подзапросу, чтобы дополнительно различать вычисление «rowpos», чтобы не генерировались нити, или добавить дополнительное предложение порядка во внешний запрос, чтобы разорвать связь. - person Bob Mc; 19.07.2011
comment
Разве этот случай не быстрее, чем решение ROW_NUMBER, даже если ваша БД предлагает ROW_NUMBER? Я предполагаю, что этот способ - O (n) против O (n * log (n)) (получение номера строки) - person Flati; 02.01.2017
comment
Возможно. Я думаю, все зависит от того, как поставщик реализует ROW_NUMBER. Похоже, что для SQL Server существуют задокументированные свидетельства проблем с производительностью с ROW_NUMBER при работе с большими наборами данных, что означает, что мое решение в этой ситуации работает быстрее. См. ответ @Aaronaught здесь. Я думаю, что многое будет зависеть и от того, как оптимизирован коррелированный подзапрос. Вероятно, вам придется провести собственный бенчмаркинг, чтобы быть уверенным. - person Bob Mc; 02.01.2017

arrow_upward
6
arrow_downward

Вы не указываете, какую СУБД вы используете, но если вы используете SQL Server 2005 или более позднюю версию, вы можете использовать функцию ROW_NUMBER(), например:

SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) AS RowNumber
FROM   MyTable

Затем, чтобы получить данную строку, вы можете использовать производную таблицу или обычное табличное выражение, например:

;WITH NumberedRows
AS
(SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) AS RowNumber
FROM    MyTable)
SELECT RowNumber FROM NumberedRows WHERE id = 47

Чтобы определить, на какой странице находится запись, вам нужно разделить номер строки на количество записей на странице и округлить до ближайшего целого числа.

person Aaron Alton    schedule 03.07.2009

arrow_upward
2
arrow_downward

Предполагая SQL 2005, вы можете использовать Row_Number

person Jhonny D. Cano -Leftware-    schedule 03.07.2009

arrow_upward
1
arrow_downward

Ответ зависит от того, какую версию SQL вы используете.

Если вы используете MSSQL 2005, вы можете использовать новую аналитическую функцию ROW_NUMBER (), которая генерирует порядковый номер в порядке, который мы определяем в предложении order by. Синтаксис функции ROW_NUMBER():

ROW_NUMBER () БОЛЕЕ (ЗАКАЗАТЬ ПО)

or

ROW_NUMBER () OVER (РАЗДЕЛЕНИЕ ПО)

Если вы используете более старую версию SQL Server, вы можете создать временную таблицу со столбцом идентификаторов и выбрать из нее результаты.

person chinna    schedule 03.07.2009

arrow_upward
1
arrow_downward

использовать Row_Number

person joe    schedule 03.07.2009