Мне интересно узнать некоторые (в идеале) независимые от базы данных способы выбора n-ой строки из таблицы базы данных. Также было бы интересно посмотреть, как этого можно достичь, используя родную функциональность следующих баз данных:
В настоящее время я делаю нечто подобное в SQL Server 2005, но мне было бы интересно увидеть другие, более гибкие подходы:
WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000
Кредит за вышеупомянутый SQL: Firoz Ansari's Weblog
Обновление: См. ответ Troels Arvin's answer относительно стандарта SQL. *Троэлс, у вас есть какие-нибудь ссылки, которые мы можем привести?
Есть способы сделать это в необязательных частях стандарта, но многие базы данных поддерживают свой собственный способ сделать это.
Действительно хороший сайт, который говорит об этом и других вещах, это < http://troels.arvin.dk/db/rdbms/#select-limit>.
В основном PostgreSQL и MySQL поддерживают нестандартные:
SELECT...
LIMIT y OFFSET x
Oracle, DB2 и MSSQL поддерживают стандартные функции окон:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
(который я только что скопировал с сайта, упомянутого выше, так как я никогда не использую эти DB)
Обновление: * Начиная с PostgreSQL 8.4 поддерживаются стандартные функции окон, поэтому ожидайте, что второй пример будет работать и для PostgreSQL.
Обновление: * Поддержка оконных функций с добавлением SQLite в версии 3.25.0 2018-09-15, поэтому обе формы также работают в SQLite.
PostgreSQL поддерживает [функции окон] [функции окон], как определено стандартом SQL, но они неудобны, поэтому большинство людей используют (нестандартные) LIMIT
/ OFFSET
:
SELECT
*
FROM
mytable
ORDER BY
somefield
LIMIT 1 OFFSET 20;
Этот пример выбирает 21-ю строку. «OFFSET 20» говорит Postgres пропустить первые 20 записей. Если вы не укажете пункт «ORDER BY», нет гарантии, какую запись вы получите обратно, что редко полезно.
Я не уверен ни в одном из остальных, но я знаю, что SQLite и MySQL не имеют порядка строк по умолчанию. По крайней мере, на этих двух диалектах следующий фрагмент захватывает 15-ю запись из таблицы, сортируя по дате / времени, когда она была добавлена:
SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15
(конечно, вам нужно иметь добавленное поле DATETIME и установить его в дату / время добавления записи...)
SQL 2005 и выше имеет эту функцию встроенной. Используйте функцию ROW_NUMBER (). Отлично подходит для веб-страниц с < < Предыдущая и Следующая > > просмотр стиля:
Синтаксис:
SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
Я подозреваю, что это дико неэффективно, но довольно простой подход, который работал над небольшим набором данных, над которым я его попробовал.
select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc
Это получит 5-й элемент, изменит второй верхний номер, чтобы получить другой n-й элемент
Только на сервере SQL (я думаю), но он должен работать на старых версиях, которые не поддерживают ROW_NUMBER ().
Проверьте это на SQL Server:
Select top 10 * From emp
EXCEPT
Select top 9 * From emp
Это даст вам 10-й ряд таблицы emp!
1 небольшое изменение: n-1 вместо n.
select *
from thetable
limit n-1, 1
Вопреки утверждениям некоторых ответов, стандарт SQL не молчит по этому вопросу.
Начиная с SQL: 2003, вы можете использовать «оконные функции» для пропуска строк и ограничения наборов результатов.
А в SQL: 2008 был добавлен немного более простой подход с использованием
ОФСЕТ пропустить СТРОКИ
FETCH FIRST n ROWS ONLY < / code >
Лично я не думаю, что добавление SQL: 2008 действительно было необходимо, поэтому, если бы я был ISO, я бы не стал использовать его в довольно большом стандарте.
Когда мы работали в MSSQL 2000, мы делали то, что мы называли «тройным провалом»:
СРЕДАН
DECLARE @InnerPageSize int
DECLARE @OuterPageSize int
DECLARE @Count int
SELECT @Count = COUNT(<column>) FROM <TABLE>
SET @InnerPageSize = @PageNum * @PageSize
SET @OuterPageSize = @Count - ((@PageNum - 1) * @PageSize)
IF (@OuterPageSize < 0)
SET @OuterPageSize = 0
ELSE IF (@OuterPageSize > @PageSize)
SET @OuterPageSize = @PageSize
DECLARE @sql NVARCHAR(8000)
SET @sql = 'SELECT * FROM
(
SELECT TOP ' + CAST(@OuterPageSize AS nvarchar(5)) + ' * FROM
(
SELECT TOP ' + CAST(@InnerPageSize AS nvarchar(5)) + ' * FROM <TABLE> ORDER BY <column> ASC
) AS t1 ORDER BY <column> DESC
) AS t2 ORDER BY <column> ASC'
PRINT @sql
EXECUTE sp_executesql @sql
Это не было элегантно, и это не было быстро, но это работало.
SQL SERVER
Выберите n 'th запись сверху
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
выберите n 'th запись снизу
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Oracle:
select * from (select foo from bar order by foo) where ROWNUM = x
В Oracle 12c вы можете использовать опцию OFFSET .. FETCH .. ROWS
с ORDER BY
Например, чтобы получить 3-ю запись сверху:
SELECT *
FROM sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
Вот быстрое решение вашей путаницы.
SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1
Здесь вы можете получить последний ряд, заполнив N = 0, второй последний - N = 1, четвертый последний - заполнив N = 3 и так далее.
Это очень распространенный вопрос по поводу интервью, и это очень просто.
Далее Если вам нужна сумма, идентификатор или какой-либо порядок числовой сортировки, вы можете перейти на функцию CAST в MySQL
SELECT DISTINCT (`amount`) FROM cart ORDER BY CAST( `amount` AS SIGNED ) DESC LIMIT 4 , 1
Здесь, заполнив N = 4, вы сможете получить пятую последнюю запись самой высокой суммы из таблицы CART. Вы можете соответствовать своему полю и названию таблицы и придумать решение.
ADD:
LIMIT n,1
Это ограничит результаты одним результатом, начиная с результата n.
Например, если вы хотите выбрать каждую 10-ю строку в MSSQL, вы можете использовать ;
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
FROM TableName
) AS foo
WHERE rownumber % 10 = 0
Просто возьмите MOD и измените номер 10 здесь на любой номер, который вы хотите.
Для SQL Server общий способ выбора номера строки:
SET ROWCOUNT @row --@row = the row number you wish to work on.
Например:
set rowcount 20 --sets row to 20th row
select meat, cheese from dbo.sandwich --select columns from table at 20th row
set rowcount 0 --sets rowcount back to all rows
Это вернет информацию 20-го ряда. Обязательно вставьте число строк 0 позже.
LIMIT n,1 не работает в MS SQL Server. Я думаю, что это практически единственная крупная база данных, которая не поддерживает этот синтаксис. Справедливости ради следует отметить, что он не является частью стандарта SQL, хотя он настолько широко поддерживается, что должен быть. Во всех базах данных, кроме SQL-сервера, LIMIT работает отлично. Для SQL-сервера я не смог найти элегантного решения.
Вот общая версия программы, которую я недавно написал для Oracle и которая позволяет динамическую пагинацию/сортировку - HTH
-- p_LowerBound = first row # in the returned set; if second page of 10 rows,
-- this would be 11 (-1 for unbounded/not set)
-- p_UpperBound = last row # in the returned set; if second page of 10 rows,
-- this would be 20 (-1 for unbounded/not set)
OPEN o_Cursor FOR
SELECT * FROM (
SELECT
Column1,
Column2
rownum AS rn
FROM
(
SELECT
tbl.Column1,
tbl.column2
FROM MyTable tbl
WHERE
tbl.Column1 = p_PKParam OR
tbl.Column1 = -1
ORDER BY
DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 1, Column1, 'X'),'X'),
DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 1, Column1, 'X'),'X') DESC,
DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate),
DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate) DESC
))
WHERE
(rn >= p_lowerBound OR p_lowerBound = -1) AND
(rn <= p_upperBound OR p_upperBound = -1);
Но на самом деле, не все ли это на самом деле просто хитрости для хорошего дизайна базы данных? Несколько раз мне нужна была такая функциональность, как для простого одноразового запроса, чтобы сделать быстрый отчет. Для любой реальной работы использование таких трюков вызывает проблемы. Если требуется выбор конкретной строки, просто имейте столбец с последовательным значением и покончите с ним.
SELECT * FROM emp a
WHERE n = (SELECT COUNT( _rowid)
FROM emp b
WHERE a. _rowid >= b. _rowid);