22,210
社区成员
发帖
与我相关
我的任务
分享
Select * From (
Select *, (Row_Number() OVER (PARTITION BY ptype Order by PtopNum DESC ,pdate DESC)) as Row_Num From News
) ntb Where Row_Num<=@Count
declare @temp int
declare cur cursor for select distinct ptype from News
open cur
WHILE @@FETCH_STATUS <>-1
begin
FETCH NEXT FROM cur INTO @temp
select top(@Count)* from News where PType=@temp order by PtopNum DESC ,pdate DESC
end
CLOSE cur
DEALLOCATE cur
用row_number()
CREATE NONCLUSTERED INDEX [iDate_Top] ON [dbo].[News]
(
[PDate] DESC,
[PtopNum] DESC
)
INCLUDE ( [PType]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Select * From
(
Select *, Row_Number() OVER (PARTITION BY ptype Order by PtopNum DESC ,pdate DESC) as Row_Num From News
) ntb
Where Row_Num<=@Count