Jak wybrać n-ty wiersz w tabeli bazy danych SQL?
Jestem zainteresowany poznaniem niektórych (najlepiej) sposobów wyboru N TH wiersza z tabeli bazy danych. Byłoby również interesujące zobaczyć, jak można to osiągnąć przy użyciu natywnej funkcjonalności następujących baz danych:
- SQL Server
- MySQL
- PostgreSQL
- SQLite
- Oracle
Obecnie robię coś takiego w SQL Server 2005, ale chciałbym zobaczyć inne bardziej agnostyczne "approaches": {]}
WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000
Kredyt na powyższy SQL: Firoz Ansari ' s Weblog
Update: Zobacz odpowiedź Troelsa Arvina odnośnie standardu SQL. Troels, masz jakieś linki, które możemy przytoczyć?
29 answers
Istnieją sposoby zrobienia tego w opcjonalnych częściach standardu, ale wiele baz danych obsługuje swój własny sposób.
Naprawdę dobra strona, która mówi o tym i innych rzeczach jest http://troels.arvin.dk/db/rdbms/#select-limit .
Zasadniczo PostgreSQL i MySQL obsługuje niestandardowe:
SELECT...
LIMIT y OFFSET x
Oracle, DB2 i MSSQL obsługuje standardowe funkcje okienkowe:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
(które właśnie skopiowałem ze strony podlinkowanej powyżej, ponieważ nigdy nie używam te DBs)
Update: począwszy od PostgreSQL 8.4 standardowe funkcje okien są obsługiwane, więc spodziewaj się, że drugi przykład będzie działał również dla PostgreSQL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-18 15:41:10
The LIMIT
/ OFFSET
składnia w PostgreSQL to:
SELECT
*
FROM
mytable
ORDER BY
somefield
LIMIT 1 OFFSET 20;
Ten przykład wybiera 21 wiersz. OFFSET 20
mówi Postgresowi, aby pominął pierwsze 20 rekordów. Jeśli nie podasz klauzuli ORDER BY
, nie ma gwarancji, który rekord otrzymasz, co jest rzadko przydatne.
Najwyraźniej standard SQL milczy w kwestii limitu poza szalonymi funkcjami okien, dlatego każdy implementuje go inaczej.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-14 10:11:59
Nie jestem pewien co do reszty, ale wiem, że SQLite i MySQL nie mają "domyślnego" porządkowania wierszy. W tych dwóch dialektach co najmniej następujący fragment chwyta piętnasty wpis z the_table, sortując według daty / czasu, który został dodany:
SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15
(oczywiście musisz mieć dodane pole DATETIME i ustawić je na datę / czas dodania wpisu...)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 17:20:33
SQL 2005 i nowsze mają tę funkcję wbudowaną. Użyj funkcji ROW_NUMBER (). Jest idealny dla stron internetowych z > style przeglądania:
Składnia:
SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-14 10:16:27
Podejrzewam, że jest to szalenie nieefektywne, ale jest to dość proste podejście, które działało na małym zbiorze danych, na którym go wypróbowałem.
select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc
Otrzymamy 5-ty element, zmienimy drugi numer na inny n-ty element
SQL server tylko (myślę), ale powinien działać na starszych wersjach, które nie obsługują ROW_NUMBER ().
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 17:34:38
1 mała zmiana: n-1 zamiast n.
select *
from thetable
limit n-1, 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-03 23:35:02
Zweryfikuj to na SQL Server:
Select top 10 * From emp
EXCEPT
Select top 9 * From emp
To daje 10 wiersz tabeli emp!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-04-09 20:25:14
Wbrew temu, co twierdzą niektóre odpowiedzi, standard SQL nie milczy w tym temacie.
Od SQL: 2003, możesz używać "funkcji okna" do pomijania wierszy i ograniczania zestawów wyników.
I w SQL: 2008 dodano nieco prostsze podejście, używając
OFFSET skip ROWS
FETCH FIRST n ROWS ONLY
Osobiście nie sądzę, aby dodanie SQL:2008 było naprawdę potrzebne, więc gdybym był ISO, trzymałbym go z dala od już dość dużego standardu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-20 19:21:48
Wyrocznia:
select * from (select foo from bar order by foo) where ROWNUM = x
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 18:51:29
Kiedy pracowaliśmy w MSSQL 2000, robiliśmy coś, co nazwaliśmy "triple-flip":
Edytowane
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
Nie był elegancki i nie był szybki, ale zadziałał.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-30 15:31:24
SQL SERVER
Select n ' TH record from top
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Select n ' TH record from bottom
SELECT * FROM (
SELECT
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE
) AS TMP
WHERE ROW = n
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-02-19 04:35:17
Oto szybkie rozwiązanie twojego zamieszania.
SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1
Tutaj możesz uzyskać ostatni wiersz wypełniając N=0, drugi ostatni przez N=1, czwarty ostatni przez wypełnienie N = 3 i tak dalej.
To jest bardzo częste pytanie podczas wywiadu i to jest bardzo proste.Dalej jeśli chcesz Amount, ID lub jakąś numeryczną Kolejność sortowania niż u może przejść do funkcji CAST w MySQL.
SELECT DISTINCT (`amount`) FROM cart ORDER BY CAST( `amount` AS SIGNED ) DESC LIMIT 4 , 1
Tutaj wypełniając N = 4 będziesz mógł uzyskać piąty ostatni rekord najwyższej kwoty z tabeli koszyka. Ty może dopasować nazwę pola i tabeli i wymyślić rozwiązanie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-17 09:29:27
Dodaj:
LIMIT n,1
, który ograniczy wyniki do jednego wyniku rozpoczynającego się od wyniku n.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 17:14:05
LIMIT N, 1 nie działa w MS SQL Server. Myślę, że to jedyna duża baza danych, która nie obsługuje tej składni. Szczerze mówiąc, nie jest to część standardu SQL, chociaż jest tak szeroko wspierany, że powinien być. We wszystkim oprócz SQL server LIMIT działa świetnie. Dla SQL server nie udało mi się znaleźć eleganckiego rozwiązania.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 17:18:06
Oto ogólna wersja sproc, którą niedawno napisałem dla Oracle, która pozwala na dynamiczne stronicowanie / sortowanie-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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 17:19:01
Ale naprawdę, czy to wszystko nie jest tak naprawdę tylko sztuczki dla dobrego projektowania bazy danych w pierwszej kolejności? Kilka razy potrzebowałem takiej funkcjonalności, to było proste jednorazowe zapytanie, aby zrobić szybki raport. W przypadku każdej prawdziwej pracy używanie takich sztuczek jest kłopotliwe. Jeśli wybranie konkretnego wiersza jest potrzebne, wystarczy mieć kolumnę z wartością sekwencyjną i zrobić z nią.
Na przykład, jeśli chcesz wybrać co 10 wiersz w MSSQL, możesz użyć;
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
FROM TableName
) AS foo
WHERE rownumber % 10 = 0
Po prostu weź MOD i zmień numer 10 tutaj, jaki chcesz.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-30 08:41:20
In Sybase SQL Anywhere:
SELECT TOP 1 START AT n * from table ORDER BY whatever
Nie zapomnij o zamówieniu przez lub jest bez znaczenia.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-19 19:06:59
Dla SQL Server, ogólnym sposobem przechodzenia przez numer wiersza jest: Ustaw ROWCOUNT @ row --@row = numer wiersza, nad którym chcesz pracować.
Na Przykład:
Set rowcount 20 --ustawia wiersz na 20. wiersz
Wybierz mięso, ser z dbo.sandwich --select columns from table at 20th row
Set rowcount 0 --ustawia rowcount z powrotem do wszystkich wierszy
Zwróci to informacje z 20.wiersza. Pamiętaj, aby umieścić w rowcount 0 później.
Znam noobish, ale jestem SQL noob i ja go używałem, więc co mogę powiedzieć?
T-SQL-wybór N ' numeru rekordu z tabeli
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
Aby np. wybrać 5 rekord z tabeli, Twoje zapytanie powinno być
select * from
(select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-26 12:46:41
SELECT * FROM emp a
WHERE n = (SELECT COUNT( _rowid)
FROM emp b
WHERE a. _rowid >= b. _rowid);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-03-20 15:48:43
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
Napisałem to zapytanie, aby znaleźć N-ty wiersz. Przykład z tym zapytaniem to
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-14 09:52:07
Niewiarygodne, że można znaleźć silnik SQL wykonujący ten ...
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-03-20 15:48:30
Nic wymyślnego, żadnych specjalnych funkcji, w przypadku, gdy używasz Caché tak jak ja...
SELECT TOP 1 * FROM (
SELECT TOP n * FROM <table>
ORDER BY ID Desc
)
ORDER BY ID ASC
Biorąc pod uwagę, że masz kolumnę ID lub kolumnę datestamp, której możesz zaufać.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-01 13:16:54
Tak zrobiłbym to w DB2 SQL, wierzę, że RRN (względny numer rekordu) jest przechowywany w tabeli przez O / S;
SELECT * FROM (
SELECT RRN(FOO) AS RRN, FOO.*
FROM FOO
ORDER BY RRN(FOO)) BAR
WHERE BAR.RRN = recordnumber
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-18 15:12:02
select * from
(select * from ordered order by order_id limit 100) x order by
x.order_id desc limit 1;
Najpierw wybierz top 100 wierszy, zamawiając rosnąco, a następnie wybierz ostatni wiersz, zamawiając malejąco i ogranicz do 1. Jest to jednak bardzo kosztowne oświadczenie, ponieważ uzyskuje dostęp do danych dwukrotnie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-02 20:26:39
Wydaje mi się, że aby być wydajnym, musisz 1) wygenerować losową liczbę od 0 do o jeden mniej niż liczba rekordów bazy danych, oraz 2) BYĆ w stanie wybrać wiersz na tej pozycji. Niestety, różne bazy danych mają różne generatory liczb losowych i różne sposoby wybierania wiersza na pozycji w zestawie wyników-zwykle określa się, ile wierszy pominąć i ile wierszy chcesz, ale to robi się inaczej dla różnych baz danych. Oto coś, co działa dla mnie w SQLite:
select *
from Table
limit abs(random()) % (select count(*) from Words), 1;
To zależy od możliwości użycia zapytania podrzędnego w klauzuli limit (która w SQLite to LIMIT
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-17 07:46:08
W Oracle 12c możesz użyć opcji OFFSET..FETCH..ROWS
z ORDER BY
Na przykład, aby uzyskać trzeci rekord od góry:
SELECT *
FROM sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-05 12:43:42
Dla SQL server, następujący wiersz zwróci pierwszy wiersz z tabeli.
declare @rowNumber int = 1;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
Możesz zapętlić wartości za pomocą czegoś takiego:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-21 15:45:35