Как выбрать энную строку в таблице базы данных SQL?

Я интересуюсь изучением некоторых (идеально) агностик базы данных способы выбрать энную строку из таблицы базы данных. Также было бы интересно видеть, как это может быть достигнуто с помощью стандартной функциональности следующих баз данных:

  • SQL Server
  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle

Я в настоящее время делаю что-то как следующее в 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

Обновление: См., что ответ Troels Arvin расценивает стандарт SQL. Troels, Вы получили какие-либо ссылки, которые мы можем процитировать?

373
задан Charles Roper 19 July 2019 в 12:51
поделиться

19 ответов

Существуют способы сделать это в дополнительных частях стандарта, но много баз данных поддерживает их собственный способ сделать его.

А действительно хороший сайт, который говорит об этом и других вещах, 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

(который я просто скопировал с сайта, связанного выше, так как я никогда не использую тех DBS)

Обновление: С PostgreSQL 8.4 стандартные функции работы с окнами поддерживаются, поэтому ожидайте, что второй пример будет работать на PostgreSQL также.

Обновление: SQLite добавил поддержку функций окна в версии 3.25.0 15.09.2018, таким образом, обе формы также работают в SQLite.

329
ответ дан user1461607 23 November 2019 в 00:01
поделиться

невероятный, что можно найти механизм 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
0
ответ дан Michael Fredrickson 23 November 2019 в 00:01
поделиться

В SQL Sybase Где угодно:

SELECT TOP 1 START AT n * from table ORDER BY whatever

не забывают ORDER BY, или это бессмысленно.

1
ответ дан Graeme Perrow 23 November 2019 в 00:01
поделиться

Вот универсальная версия 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);
2
ответ дан Greg Hurlman 23 November 2019 в 00:01
поделиться

Но действительно, все это не действительно просто светские таланты для хорошего проектирования баз данных во-первых? Несколько раз мне была нужна функциональность как это, это было для простого от запроса для создания быстрого отчета. Для любой реальной работы, с помощью приемов как они приглашает проблему. Если выбор конкретной строки необходим, тогда просто имеют столбец с последовательным значением и сделаны с ним.

2
ответ дан 23 November 2019 в 00:01
поделиться

LIMIT / OFFSET синтаксис в PostgreSQL:

SELECT
    *
FROM
    mytable
ORDER BY
    somefield
LIMIT 1 OFFSET 20;

Этот пример выбирает 21-ю строку. OFFSET 20 говорит Пост-ГРЭС пропускать первые 20 записей. Если Вы не определяете ORDER BY пункт, нет никакой гарантии, которые записывают Вас, возвратится, который редко полезен.

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

94
ответ дан Neall 23 November 2019 в 00:01
поделиться

Я не уверен ни в одном из остальных, но я знаю, что SQLite и MySQL не имеют никакого упорядочивания строки "по умолчанию". На тех двух диалектах, по крайней мере, следующий отрывок захватывает 15-ю запись от the_table, сортирующего по дате/времени, это было добавлено:

SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15

(конечно, Вы должны были бы иметь добавленное поле DATETIME и установить его на дату/время, что запись была добавлена...)

28
ответ дан Ellen Teapot 23 November 2019 в 00:01
поделиться

Я подозреваю, что это дико неэффективно, но является вполне простым подходом, который работал над небольшим набором данных, что я примерил его.

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

Это получило бы 5-й объект, изменило бы второе главное число для получения различного энного объекта

SQL-сервер только (я думаю), но должен работать над более старыми версиями, которые не поддерживают ROW_NUMBER ().

17
ответ дан Tim Saunders 23 November 2019 в 00:01
поделиться

ОГРАНИЧЬТЕ n, 1 не работает в SQL Server MS. Я думаю, что это - примерно единственная главная база данных, которая не поддерживает тот синтаксис. Честно говоря, это не часть стандарта SQL, хотя это так широко поддерживается, что это должно быть. Во всем кроме SQL-сервера ПРЕДЕЛ работает отлично. Для SQL-сервера я не был в состоянии найти изящное решение.

2
ответ дан Kibbee 23 November 2019 в 00:01
поделиться

1 небольшое изменение: n-1 вместо n.

select *
from thetable
limit n-1, 1
13
ответ дан user1357154 23 November 2019 в 00:01
поделиться

Oracle:

select * from (select foo from bar order by foo) where ROWNUM = x
5
ответ дан Mark Harrison 23 November 2019 в 00:01
поделиться

Когда мы раньше работали в 2000 MSSQL, мы сделали то, что мы назвали "тройным зеркальным отражением":

ОТРЕДАКТИРОВАЛ

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

, Это не было изящно, и это не было быстро, но это работало.

7
ответ дан Adam V 23 November 2019 в 00:01
поделиться

ADD:

LIMIT n,1

, Который ограничит результаты одним результатом, запускающимся в результате n.

3
ответ дан Andrew G. Johnson 23 November 2019 в 00:01
поделиться

Вопреки тому, что часть требования ответов, стандарт SQL не тих относительно этого предмета.

Начиная с SQL:2003, Вы были в состоянии использовать "функции окна", чтобы пропустить строки и ограничить наборы результатов.

И в SQL:2008, немного более простой подход был добавлен, с помощью
OFFSET skip ROWS FETCH FIRST n ROWS ONLY

Лично, я не думаю, что дополнение SQL:2008 было действительно необходимо, поэтому если бы я был ISO, я не допустил бы его в уже довольно большой стандарт.

11
ответ дан Troels Arvin 23 November 2019 в 00:01
поделиться

Можно использовать этого:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n
0
ответ дан 23 November 2019 в 00:01
поделиться
SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);
1
ответ дан 23 November 2019 в 00:01
поделиться

Для 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.

2
ответ дан 23 November 2019 в 00:01
поделиться

В SQL 2005 и более поздних версиях эта функция встроена. Используйте функцию ROW_NUMBER (). Он отлично подходит для просмотра веб-страниц в стиле << Назад и Далее >>:

Синтаксис:

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23
22
ответ дан 23 November 2019 в 00:01
поделиться

Самый подходящий ответ я видел на этот статья для SQL-сервера

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 3
0
ответ дан 23 November 2019 в 00:01
поделиться
Другие вопросы по тегам:

Похожие вопросы: