SQL 选择除顶行以外的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15032803/
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
select all rows except top row
提问by D-Dawgg
how do I return all rows from a table except the first row. Here is my sql statement:
如何从表中返回除第一行以外的所有行。这是我的sql语句：
Select Top(@TopWhat) *
from tbl_SongsPlayed
where Station = @Station
order by DateTimePlayed DESC
How do I alter my SQL statement to return all rows except the first row.
如何更改我的 SQL 语句以返回除第一行之外的所有行。
Many thanks
非常感谢
回答by chrisb
SQL 2012 also has the rather handy OFFSET clause:
SQL 2012 也有相当方便的 OFFSET 子句：
Select Top(@TopWhat) *
from tbl_SongsPlayed
where Station = @Station
order by DateTimePlayed DESC
OFFSET 1 ROWS
回答by Taryn
Depending on your database product, you can use row_number():
根据您的数据库产品，您可以使用row_number()：
select *
from
(
Select s.*,
row_number() over(order by DateTimePlayed DESC) rn
from tbl_SongsPlayed s
where s.Station = @Station
) src
where rn >1
回答by Som Poddar
already 'Chrisb' has given a very neat answer. But you can also try this one...
'Chrisb' 已经给出了一个非常简洁的答案。但是你也可以试试这个...
The EXCEPT operand (http://msdn.microsoft.com/en-us/library/ms188055.aspx)
EXCEPT 操作数 ( http://msdn.microsoft.com/en-us/library/ms188055.aspx)
Select Top(@TopWhat) *
from tbl_SongsPlayed
Except Select Top(1) *
from tbl_SongsPlayed
where Station = @Station
order by DateTimePlayed DESC
'Not In' was another clause that can be used.
'Not In' 是另一个可以使用的子句。
回答by Ann L.
Assuming you have a unique ID for tbl_SongsPlayed, you could do something like this:
假设您有一个唯一的 ID tbl_SongsPlayed，您可以执行以下操作：
// Filter the songs first
With SongsForStation
As (
Select *
From tbl_SongsPlayed
Where Station = @Station
)
// Get the songs
Select *
From SongsForStation
Where SongPlayId <> (
// Get the top song, most recently played, so you can exclude it.
Select Top 1 SongPlayId
From SongsForStation
Order By DateTimePlayed Desc
)
// Sort the rest of the songs.
Order By
DateTimePlayed desc
Where
