SQL 查询:如何直接获取编号为 1000 的行的数据? [英] SQL Query: How can I get data of row with number 1000 direclty?

问题描述

如果我有一个名为 Persons 的 SQL 表，其中包含大约 30000 行，并且我想做一个 SQL 查询来检索行号 1000 的数据...我通过非专业方式得到它进行以下查询

If I have a SQL Table called Persons that contain about 30000 rows and I want to make a SQL query that retrieve the data of row number 1000 ... I got it by non professional way by making the following query

Select Top 1 * from 
(
    Select top 1000 *
    From Persons
    Order By ID 
)A
Order By A.ID desc

但我觉得这是一个更优化的查询，可以做到这一点......任何可以引导我完善查询吗?
注意:表包含名为ID"的 PK 列，但它不是连续的

But I feel that's a more optimized query that can do that ... can any lead me to perfect query ?
Note : table contain PK column called "ID" but it's not sequential

推荐答案

row_number 是最好的方法，但由于您只想要一行，请务必查看计划.识别所需的行然后连接回原始表以检索其他列可能会更好.

row_number is the best approach but as you only want a single row be sure to look at the plan. It might turn out better to identify the desired row then join back onto the original table to retrieve additional columns.

WITH T1
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY number) AS RN
         FROM   master..spt_values)
SELECT name,
       number,
       type,
       low,
       high,
       status
FROM   T1
WHERE  RN = 1000;

给予

表'spt_values'.扫描计数 1，逻辑读取 2005

Table 'spt_values'. Scan count 1, logical reads 2005

CPU 时间 = 0 毫秒，运行时间 = 19 毫秒.

CPU time = 0 ms, elapsed time = 19 ms.

WITH T2
     AS (SELECT number,
                type,
                name,
                ROW_NUMBER() OVER (ORDER BY number) AS RN
         FROM   master..spt_values)
SELECT TOP 1 C.name,
             C.number,
             C.type,
             C.low,
             C.high,
             C.status
FROM   T2
       CROSS APPLY (SELECT *
                    FROM   master..spt_values v
                    WHERE  v.number = T2.number
                           AND v.type = T2.type
                           AND ( v.name = T2.name
                                  OR ( v.name IS NULL
                                       AND T2.name IS NULL ) )) C
WHERE  RN = 1000;  

给予

表'spt_values'.扫描计数 1，逻辑读取 7

Table 'spt_values'. Scan count 1, logical reads 7

CPU 时间 = 0 毫秒，经过的时间 = 1 毫秒.

CPU time = 0 ms, elapsed time = 1 ms.

这篇关于SQL 查询:如何直接获取编号为 1000 的行的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！