如何从表中返回除第一行以外的所有行。 这是我的sql语句:
Select Top(@TopWhat) *
from tbl_SongsPlayed
where Station = @Station
order by DateTimePlayed DESC
如何修改我的SQL语句以返回除第一行之外的所有行。
非常感谢
如何从表中返回除第一行以外的所有行。 这是我的sql语句:
Select Top(@TopWhat) *
from tbl_SongsPlayed
where Station = @Station
order by DateTimePlayed DESC
如何修改我的SQL语句以返回除第一行之外的所有行。
非常感谢
SQL 2012 还有一个非常方便的 OFFSET 子句:
Select Top(@TopWhat) *
from tbl_SongsPlayed
where Station = @Station
order by DateTimePlayed DESC
OFFSET 1 ROWS
根据您使用的数据库产品,您可以使用 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
order by
语句是 order by DateTimePlayed desc
。 - Gordon Linoff已经有'Chrisb'提供了一个非常简洁的答案。但是您也可以尝试使用以下方法...
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
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