选择除顶行以外的所有行[重复]

This question already has an answer here:

这个问题在这里已有答案:

selecting the Row of table Except the First one 3 answers

选择表格行除了第一个3个答案

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

4 个解决方案

#1

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

#2

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

#3

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'是另一个可以使用的条款。

#4

Assuming you have a unique ID for tbl_SongsPlayed, you could do something like this:

假设你有一个tbl_SongsPlayed的唯一ID,你可以这样做:

// 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

#1