在SQL表中选择第2和第3行。

i'm currently developing a news-site. So here is the problem. I want to select the 2nd and 3rd row in a TOP 3.

我正在开发一个新闻网站。这就是问题所在。我想在前3中选择第二和第三行。

SELECT TOP 3 * FROM News ORDER BY Date DESC;

I want to remove the 1st row and only return the 2nd and 3rd row.

我要删除第一行，只返回第2和第3行。

Can anyone help?

谁能帮忙吗?

5 个解决方案

#1

Try this:

试试这个:

SELECT TOP 2 FROM
( SELECT TOP 3 * FROM News ORDER BY Date DESC ) xx
ORDER BY Date

SQLFiddle: http://www.sqlfiddle.com/#!3/dbb7e/5

SQLFiddle:http://www.sqlfiddle.com/ ! 3 / dbb7e / 5

#2

You can also do this generically using window functions:

您也可以使用窗口函数来做这个通用的操作:

select n.*
from (SELECT n.*, row_number() over (order by date desc) as seqnum
      FROM News n
     ) n
where n.seqnum >= 2 and n.seqnum <= 3;

I just offer this as a general solution. You can also ensure that you get everything from the second date (in case there are more than two items on that date) by using dense_rank() rather than row_number().

我只是把它作为通解。您还可以通过使用dense_rank()而不是row_number()来确保从第2个日期(在那个日期有超过两个条目)得到所有内容。

#3

If you know that no two dates will be the same, you could add

如果你知道没有两个日期是一样的，你可以加上。

where date not in (select max(date) from News)

Or you could look at rowid if you know that the first item will have rowid=0, for example if you created a temp table with the results of your initial query.

或者您可以查看rowid，如果您知道第一项将有rowid=0，例如，如果您创建了一个带有初始查询结果的临时表。

#4

I assume, you somehow know what you Top 3 news are by ordering by date descending. Therfore you should use the LIMIT clause with an OFFSET [For sqlite]

我猜，你知道你最重要的3个新闻是按日期顺序排列的。因此，您应该使用带有偏移量(sqlite)的LIMIT子句

SELECT * FROM News ORDER BY Date DESC LIMIT 2 OFFSET 1;

#5

Select top 2 * from News cross apply (select top 3 from news order by date desc)x

#1