Как выбрать n-ую строку в таблице базы данных SQL?
646832 просмотра
29 ответа
Я заинтересован в изучении некоторых (в идеале) независимых от базы данных способов выбора n- й строки из таблицы базы данных. Также было бы интересно увидеть, как этого можно достичь, используя встроенную функциональность следующих баз данных:
- SQL Server
- MySQL
- PostgreSQL
- SQLite
- оракул
В настоящее время я делаю что-то вроде следующего в 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: блог Фироза Ансари
Обновление: см . Ответ Troels Arvin относительно стандарта SQL. Troels, есть ли у вас ссылки, которые мы можем цитировать?
Автор: Charles Roper Источник Размещён: 17.05.2019 02:56Ответы (29)
299 плюса
Есть способы сделать это в дополнительных частях стандарта, но многие базы данных поддерживают свой собственный способ сделать это.
Действительно хороший сайт, который говорит об этом и других вещах, это 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
(который я только что скопировал с сайта, указанного выше, поскольку я никогда не использую эти БД)
Обновление: Начиная с PostgreSQL 8.4 поддерживаются стандартные оконные функции, так что ожидайте, что второй пример будет работать и для PostgreSQL.
Обновление: SQLite добавил поддержку оконных функций в версии 3.25.0 на 2018-09-15, поэтому обе формы также работают в SQLite.
Автор: Henrik Gustafsson Размещён: 19.08.2008 07:2288 плюса
LIMIT
/ OFFSET
Синтаксис в PostgreSQL является:
SELECT
*
FROM
mytable
ORDER BY
somefield
LIMIT 1 OFFSET 20;
Этот пример выбирает 21-ую строку. OFFSET 20
говорит Postgres, чтобы пропустить первые 20 записей. Если вы не укажете ORDER BY
пункт, нет гарантии, какую запись вы вернете, что редко бывает полезно.
Очевидно, стандарт SQL ничего не говорит о предельном ограничении вне сумасшедших оконных функций, поэтому каждый реализует его по-своему.
Автор: Neall Размещён: 19.08.2008 05:3127 плюса
Я не уверен ни в одном из остальных, но я знаю, что SQLite и MySQL не имеют порядка строк по умолчанию. По крайней мере, на этих двух диалектах следующий фрагмент извлекает 15-ую запись из таблицы, сортируя по дате / времени ее добавления:
SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15
(конечно, вам нужно иметь добавленное поле DATETIME и установить в нем дату / время добавления записи ...)
Автор: Ellen Teapot Размещён: 19.08.2008 05:2019 плюса
В SQL 2005 и выше эта функция встроена. Используйте функцию ROW_NUMBER (). Отлично подходит для веб-страниц со стилем просмотра «Предыдущая и Следующая»:
Синтаксис:
SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
Автор: Ben Breen
Размещён: 09.07.2009 03:00
16 плюса
Я подозреваю, что это крайне неэффективно, но это довольно простой подход, который работал на небольшом наборе данных, на котором я его пробовал.
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 ().
Автор: Tim Saunders Размещён: 19.08.2008 05:2812 плюса
Проверьте это на SQL Server:
Select top 10 * From emp
EXCEPT
Select top 9 * From emp
Это даст вам 10-ю строку таблицы emp!
Автор: Rameshwar Pawale Размещён: 16.10.2014 10:5911 плюса
1 небольшое изменение: n-1 вместо n.
select *
from thetable
limit n-1, 1
Автор: Nick Berardi
Размещён: 19.08.2008 07:25
9 плюса
Вопреки тому, что утверждают некоторые из ответов, стандарт SQL не молчит по этому вопросу.
Начиная с SQL: 2003, вы можете использовать «оконные функции» для пропуска строк и ограничения результирующих наборов.
А в SQL: 2008 был добавлен немного более простой подход, использующий<br>
OFFSET <em>skip</em> ROWS
FETCH FIRST <em>n</em> ROWS ONLY
Лично я не думаю, что добавление SQL: 2008 действительно было необходимо, поэтому, если бы я был ISO, я бы не использовал его в уже достаточно большом стандарте.
Автор: Troels Arvin Размещён: 03.09.2008 10:396 плюса
Oracle:
select * from (select foo from bar order by foo) where ROWNUM = x
Автор: Mark Harrison
Размещён: 19.08.2008 06:51
6 плюса
Когда мы работали в 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
Это не было элегантно, и это не было быстро, но это работало.
Автор: Adam V Размещён: 19.08.2008 05:336 плюса
SQL SERVER
Выберите n-ю запись сверху
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
выберите n-ную запись снизу
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Автор: Aditya
Размещён: 19.02.2014 04:17
4 плюса
Вот быстрое решение вашей путаницы.
SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1
Здесь Вы можете получить Последнюю строку, Заполнив N = 0, Второй последний - N = 1, Четвертый Последний, Заполнив N = 3 и так далее.
Это очень распространенный вопрос во время интервью, и это очень просто.
Далее Если вы хотите Amount, ID или какой-либо Числовой Порядок сортировки, вы можете перейти к функции CAST в MySQL.
SELECT DISTINCT (`amount`) FROM cart ORDER BY CAST( `amount` AS SIGNED ) DESC LIMIT 4 , 1
Здесь, заполнив N = 4, вы сможете получить пятую последнюю запись наибольшей суммы из таблицы CART. Вы можете указать свое поле и имя таблицы и найти решение.
Автор: Amit Shah Размещён: 17.05.2012 09:292 плюса
ДОБАВЛЯТЬ:
LIMIT n,1
Это ограничит результаты одним результатом, начиная с результата n.
Автор: Andrew G. Johnson Размещён: 19.08.2008 05:142 плюса
LIMIT n, 1 не работает в MS SQL Server. Я думаю, что это единственная крупная база данных, которая не поддерживает этот синтаксис. Честно говоря, он не является частью стандарта SQL, хотя он настолько широко поддерживается, что так и должно быть. Во всем, кроме SQL-сервера LIMIT прекрасно работает. Для сервера SQL я не смог найти элегантного решения.
Автор: Kibbee Размещён: 19.08.2008 05:182 плюса
Вот общая версия sproc, которую я недавно написал для 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);
Автор: Greg Hurlman
Размещён: 19.08.2008 05:19
2 плюса
Но на самом деле, разве все это на самом деле не является просто уловками для хорошего проектирования баз данных? Несколько раз мне понадобилась такая функциональность, как простой запрос, чтобы быстро составить отчет. Для любой реальной работы использование подобных трюков - это неприятность. Если требуется выбрать конкретную строку, просто создайте столбец с последовательным значением и покончите с этим.
Автор: John Dyer Размещён: 19.08.2008 07:062 плюса
Например, если вы хотите выбрать каждую десятую строку в MSSQL, вы можете использовать;
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
FROM TableName
) AS foo
WHERE rownumber % 10 = 0
Просто возьмите мод и измените номер 10 здесь на любой номер, который вы хотите.
Автор: E-A Размещён: 30.12.2011 08:411 плюс
В Sybase SQL Anywhere:
SELECT TOP 1 START AT n * from table ORDER BY whatever
Не забывайте ORDER BY или это бессмысленно.
Автор: Graeme Perrow Размещён: 19.08.2008 07:061 плюс
Для SQL Server общий способ определения номера строки таков: SET ROWCOUNT @row - @ row = номер строки, с которой вы хотите работать.
Например:
set rowcount 20 - устанавливает строку в 20-ю строку
выберите мясо, сыр из dbo.sandwich - выберите столбцы из таблицы в 20-й строке
установить rowcount 0 - устанавливает rowcount обратно во все строки
Это вернет информацию 20-го ряда. Обязательно укажите количество строк 0 после этого.
Я знаю нубиш, но я нуб SQL, и я использовал его, так что я могу сказать?
Автор: Eric Размещён: 22.06.2009 05:001 плюс
T-SQL - выбор N-го номера записи из таблицы
select * from
(select row_number() over (order by Rand() desc) as Rno,* from TableName) T where T.Rno = RecordNumber
Where RecordNumber --> Record Number to Select
TableName --> To be Replaced with your Table Name
Например, чтобы выбрать 5-ю запись из таблицы Employee, ваш запрос должен быть
select * from
(select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5
Автор: Sangeeth Krishna
Размещён: 19.11.2010 07:02
1 плюс
SELECT * FROM emp a
WHERE n = (SELECT COUNT( _rowid)
FROM emp b
WHERE a. _rowid >= b. _rowid);
Автор: Rahul Sharma
Размещён: 08.06.2009 05:10
1 плюс
SELECT
top 1 *
FROM
table_name
WHERE
column_name IN (
SELECT
top N column_name
FROM
TABLE
ORDER BY
column_name
)
ORDER BY
column_name DESC
Я написал этот запрос для поиска N-й строки. Пример с этим запросом будет
SELECT
top 1 *
FROM
Employee
WHERE
emp_id IN (
SELECT
top 7 emp_id
FROM
Employee
ORDER BY
emp_id
)
ORDER BY
emp_id DESC
Автор: Arjun Chiddarwar
Размещён: 29.01.2015 09:09
1 плюс
В Oracle 12c Вы можете использовать OFFSET..FETCH..ROWS
опцию сORDER BY
Например, чтобы получить третью запись сверху:
SELECT *
FROM sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
Автор: Kaushik Nayak
Размещён: 05.02.2018 12:43
0 плюса
невероятно, что вы можете найти движок SQL, выполняющий этот ...
WITH sentence AS
(SELECT
stuff,
row = ROW_NUMBER() OVER (ORDER BY Id)
FROM
SentenceType
)
SELECT
sen.stuff
FROM sentence sen
WHERE sen.row = (ABS(CHECKSUM(NEWID())) % 100) + 1
Автор: jrEving
Размещён: 06.03.2009 01:30
0 плюса
Ничего особенного, никаких специальных функций, если вы используете Caché, как я ...
SELECT TOP 1 * FROM (
SELECT TOP n * FROM <table>
ORDER BY ID Desc
)
ORDER BY ID ASC
Учитывая, что у вас есть столбец ID или столбец с датой, которому вы можете доверять.
Автор: Scott Beeson Размещён: 01.10.2015 01:160 плюса
Вот как я бы это делал в DB2 SQL, я считаю, что RRN (относительный номер записи) хранится в таблице O / S;
SELECT * FROM (
SELECT RRN(FOO) AS RRN, FOO.*
FROM FOO
ORDER BY RRN(FOO)) BAR
WHERE BAR.RRN = recordnumber
Автор: RDKells
Размещён: 18.11.2016 03:12
0 плюса
select * from
(select * from ordered order by order_id limit 100) x order by
x.order_id desc limit 1;
Сначала выберите верхние 100 строк, упорядочив их по возрастанию, а затем выберите последнюю строку, упорядочив по убыванию и ограничиваясь 1. Однако это очень дорогой оператор, поскольку он обращается к данным дважды.
Автор: Dwipam Katariya Размещён: 02.06.2017 08:260 плюса
Мне кажется, что для эффективности вам необходимо: 1) сгенерировать случайное число от 0 до единицы меньше, чем количество записей в базе данных, и 2) иметь возможность выбрать строку в этой позиции. К сожалению, разные базы данных имеют разные генераторы случайных чисел и разные способы выбора строки в позиции в наборе результатов - обычно вы указываете, сколько строк пропустить и сколько строк вы хотите, но это делается по-разному для разных баз данных. Вот то, что работает для меня в SQLite:
select *
from Table
limit abs(random()) % (select count(*) from Words), 1;
Это зависит от возможности использовать подзапрос в предложении limit (в SQLite это LIMIT
0 плюса
Для SQL-сервера следующее вернет первую строку из заданной таблицы.
declare @rowNumber int = 1;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
Вы можете перебирать значения с помощью чего-то вроде этого:
WHILE @constVar > 0
BEGIN
declare @rowNumber int = @consVar;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
SET @constVar = @constVar - 1;
END;
Автор: nPcomp
Размещён: 20.06.2018 05:48
Вопросы из категории :
- mysql Двоичные данные в MySQL
- mysql Насколько большой может быть база данных MySQL до того, как производительность начнет снижаться
- mysql Выбрать все столбцы, кроме одного в MySQL?
- mysql MySQL или PDO - каковы плюсы и минусы?
- sql Проверить наличие изменений в таблице SQL Server?
- sql Обменять уникальные индексированные значения столбцов в базе данных
- sql Как работает индексация базы данных?
- sql Как индексировать столбец базы данных
- oracle Как выбрать n-ую строку в таблице базы данных SQL?
- oracle Лучший способ инкапсулировать сложную логику курсора Oracle PL / SQL в виде представления?
- oracle Лучший способ сделать многострочную вставку в Oracle?
- oracle В какую таблицу / представление вы запрашиваете выбор всех имен таблиц в схеме в Oracle?
- postgresql Как конкатенировать строки поля строки в запросе PostgreSQL?
- postgresql Возможно ли выполнять кросс-запросы к базе данных с PostgreSQL?
- postgresql Хранение изображений в PostgreSQL