SQL Server 选择前 10-20 个结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7332731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Select top 10-20 results?
提问by Nicklas
I have two columns the first one I want top 10 products (1-10)
我有两列第一列我想要前 10 名产品 (1-10)
That is
那是
SELECT TOP 10 * FROM Product
In the second column I want the next 10 results (11-20)
在第二列中，我想要接下来的 10 个结果 (11-20)
How do I do that?
我怎么做？
回答by Martin Smith
WITH T AS
(
SELECT TOP 20 name,
row_number() OVER (ORDER BY id) AS RN
FROM Products
ORDER BY id
)
SELECT
MAX(CASE WHEN RN <=10 THEN name END) AS Col1,
MAX(CASE WHEN RN > 10 THEN name END) AS Col2
FROM T
GROUP BY RN % 10
回答by sk2185
select top 10 wwwhid from wwwh where wwwhid not in(select top 10 wwwhid from wwwh)
回答by Nabmeister
I would do this:
我会这样做：
SELECT [columns] FROM [table names] ORDER BY [column name] DESC LIMIT 10 OFFSET 10;
This is simpler and less complex....
这更简单，更不复杂......
What do you guys think?
你们有什么感想？
回答by marc_s
In SQL Server, it's bit tricky to get this done. If you're on SQL Server 2005 or newer, you can use a CTE with a CROSS JOINand some trickery to get the result you're looking for:
在 SQL Server 中，完成这项工作有点棘手。如果您使用的是 SQL Server 2005 或更高版本，您可以使用带有 aCROSS JOIN和一些技巧的 CTE来获得您正在寻找的结果：
;WITH TopProducts AS
(
SELECT
ProductID, ProductName,
ROW_NUMBER() OVER(ORDER BY --some-column-here-- DESC) 'RN'
FROM dbo.Products
)
SELECT
p1.ProductID, p1.ProductName,
p2.ProductID, p2.ProductName
FROM
TopProducts p1
CROSS JOIN
TopProducts p2
WHERE
p1.RN BETWEEN 1 AND 10 -- get rows 1-10 from the first CTE
-- AND p2.RN BETWEEN 11 AND 20 redundant, as niktrs pointed out
AND p1.RN + 10 = p2.RN -- join rows from P1 and P2 so you don't get a cartesian product
The CTE (Common Table Expression) numbers the products - this needs to be based on some column from your Productstable, but you didn't mention what column defines the order.
CTE（通用表表达式）对产品进行编号 - 这需要基于Products表中的某些列，但您没有提到哪个列定义了订单。
We then select rows 1-10 from the CTE, as well as rows 11-20 from a second instance of the CTE. If you leave this as is, you'll get 100 rows - each combination of rows 1-10 from the first result set against each of the 10 rows from the second result set.
然后我们从 CTE 中选择第 1-10 行，以及从 CTE 的第二个实例中选择第 11-20 行。如果您保持原样，您将获得 100 行 - 第一个结果集中的第 1-10 行的每个组合与第二个结果集中的 10 行中的每一行。
That's why you need an additional condition, based on the row numbers, to "join" one row from each result set, and thus you'll get ten rows - the first column has the items 1-10 from the Products table, the second column has rows 11-20
这就是为什么您需要一个基于行号的附加条件来“连接”每个结果集中的一行，因此您将获得十行 - 第一列包含 Products 表中的 1-10 项，第二列列有第 11-20 行
回答by niktrs
I am not sure this is the best way to do it but it works
我不确定这是最好的方法，但它有效
select *
from
(
SELECT top 10 ROW_NUMBER() OVER(ORDER BY product) linenum, product
FROM products
) t1
JOIN
(
SELECT top 20 ROW_NUMBER() OVER(ORDER BY product) linenum, product
FROM products
) t2 ON t1.linenum+10 = t2.linenum
回答by R.Ghanbari
declare @FromRange int
declare @ToRange int
set @FromRange =11
set @ToRange =20
SELECT top(@ToRange-@FromRange+1) * FROM [tbl]
where tbl_id not in (select top (@FromRange-1) tbl_id from tbl)
ORDER BY tbl_id
回答by Access Denied
Starting with SQL server 2012 order by offset/fetchfeature:
从 SQL server 2012 order by offset/fetch功能开始：
SELECT *
FROM SomeTable
ORDER BY someIdField
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
回答by David Champion
Use the code:
使用代码：
select top 20 * from [table Name]
except
select top 10 * from [table Name]
回答by David Champion
Try this one :
试试这个：
SELECT *
FROM
(
SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY ColName) RowNo, ColName
FROM TableName
) table1
INNER JOIN
(
SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY ColName) RowNo, ColName
FROM TableName
) table2 ON table1.RowNo + 10 = table2.RowNo
回答by tonekk
SELECT * FROM Product LIMIT(10,10)
