SQL 如何从前 2 行或类似内容中选择第二行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8752458/
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
How select second line from top 2 or something similar?
提问by Bruno 'Shady'
I want to execute 2 separated commands to return me a value from my table.
我想执行 2 个分开的命令来从我的表中返回一个值。
the first one could be top 1, because is the first line, no problem...
第一个可能是前 1，因为是第一行，没问题...
but how can I make something like top 2, but only showing the second line?
但是我怎样才能制作像前 2 行这样的东西，但只显示第二行？
Is there a simple way to do it? Like one simple select?
有没有简单的方法来做到这一点？像一个简单的选择？
1 line:
1 行：
select top 1 Code from Products order by LastUpdate desc
按 LastUpdate desc 从产品订单中选择前 1 个代码
回答by Seramme
There is no generic solution to this problem, as far as I know - different DBMSes have different way of achieving this.
据我所知，这个问题没有通用的解决方案 - 不同的 DBMS 有不同的实现方式。
In Microsoft SQL Server, you can use the ROW_NUMBER clause:
在 Microsoft SQL Server 中，您可以使用 ROW_NUMBER 子句：
SELECT code FROM
(SELECT TOP 2 code, Row_Number() OVER (ORDER BY lastupdate) AS rownum
FROM Products) AS tbl
WHERE rownum = 2;
Oracle has a similar pseudo-column, called ROWNUM. However, the caveat here is that this value is computed beforethe ordering comes into play. Therefore, you would have to, once again, use a subquery:
Oracle 有一个类似的伪列，称为 ROWNUM。但是，这里需要注意的是，该值是在排序生效之前计算的。因此，您将不得不再次使用子查询：
SELECT code FROM
(SELECT code, ROWNUM rnum FROM
(SELECT code FROM Products ORDER BY lastupdate)
WHERE ROWNUM <= 2)
WHERE rnum = 2
Note that you cannot do a simple ROWNUM = 2condition here, because it would never be satisfied - ROWNUM takes into account the number of actually returnedrows, so if there never was a first returned row, ROWNUM will never reach the value '2', thus will never satisfy the condition.
请注意，您不能ROWNUM = 2在这里做一个简单的条件，因为它永远不会被满足 - ROWNUM 考虑了实际返回的行数，所以如果从来没有第一行返回，ROWNUM 永远不会达到值 '2'，因此将永远不满足条件。
In MySQL, this is even simpler:
在 MySQL 中，这更简单：
SELECT code FROM Products ORDER BY lastupdate LIMIT 2, 1
(I am not familiar with MySQL, so I am not sure if the LIMIT will be calculated before or after the ORDER BY clause - would be great if someone else could confirm this).
（我不熟悉 MySQL，所以我不确定 LIMIT 是在 ORDER BY 子句之前还是之后计算 - 如果其他人可以确认这一点会很棒）。
Other DBMSes do it in an even different way.
其他 DBMS 以一种甚至不同的方式做到这一点。
回答by Yixian
Select first row:
选择第一行：
select ... order by some_rule limit 1;
select ... order by some_rule limit 1;
Select second row:
选择第二行：
select ... order by some_rule limit 1 offset 1;
select ... order by some_rule limit 1 offset 1;
回答by Frank
To me in MS-SQL, this is simpler to remember:
对我来说，在 MS-SQL 中，这更容易记住：
Select top Nrows order desc as a "table" then select top 1 order asc
选择top N行顺序 desc 作为“表格”，然后选择前 1 个顺序 asc
SELECT TOP 1 code FROM
(SELECT TOP 2 code, lastupdate FROM Products ORDER BY lastupdate DESC) AS tblTempQuery
ORDER BY lastupdate ASC
回答by user5466439
SELECT A.CName FROM (SELECT cname from Tname ORDER BY cname desc FETCH FIRST 2 ROWS ONLY) As A order by A.cname fetch first 1 row only;
SELECT A.CName FROM (SELECT cname from Tname ORDER BY cname desc FETCH FIRST 2 ROWS ONLY) As A order by A.cname 只取前 1 行；
where CNAME = column you want to refer , Tname = table name from which u want to pull. here u can replace the value of 2 with the row u want to fetch..
其中 CNAME = 要引用的列，Tname = 要从中提取的表名。在这里你可以用你想要获取的行替换 2 的值..
