如何在SQL数据库表中选择第n行？

#1 楼

SELECT...
LIMIT y OFFSET x 

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

#2 楼

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

#3 楼

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

#4 楼

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23

#5 楼

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

#6 楼

select *
from thetable
limit n-1, 1

#7 楼

Select top 10 * From emp 
EXCEPT
Select top 9 * From emp

#8 楼

#9 楼

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

#10 楼

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

#11 楼

select * from (select foo from bar order by foo) where ROWNUM = x

#12 楼

SELECT * 
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

#13 楼

SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1

#14 楼

LIMIT n,1

#15 楼

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
  FROM TableName
) AS foo
WHERE rownumber % 10 = 0

#16 楼

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

#17 楼

#18 楼

-- 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);

#19 楼

#20 楼

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;

#21 楼

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

#22 楼

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

select * from
 (select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5

#23 楼

SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);

#24 楼

SELECT TOP 1 * FROM (
  SELECT TOP n * FROM <table>
  ORDER BY ID Desc
)
ORDER BY ID ASC

#25 楼

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

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

#26 楼

WHERE x IN (...)

SELECT TOP 1
--select the value needed from t1
[col2]
FROM
(
   SELECT TOP 2 --the Nth row, alter this to taste
   UE2.[col1],
   UE2.[col2],
   UE2.[date],
   UE2.[time],
   UE2.[UID]
   FROM
   [table1] AS UE2
   WHERE
   UE2.[col1] = ID --this is a subquery 
   AND
   UE2.[col2] IS NOT NULL
   ORDER BY
   UE2.[date] DESC, UE2.[time] DESC --sorting by date and time newest first
) AS t1
ORDER BY t1.[date] ASC, t1.[time] ASC --this reverses the order of the sort in t1

#27 楼

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

#28 楼

SELECT * FROM (                        
   SELECT RRN(FOO) AS RRN, FOO.*
   FROM FOO                         
   ORDER BY RRN(FOO)) BAR             
 WHERE BAR.RRN = recordnumber

#29 楼

select * from 
(select * from ordered order by order_id limit 100) x order by 
x.order_id desc limit 1;

#30 楼

select * 
from Table 
limit abs(random()) % (select count(*) from Words), 1;