Select * From ( Select *, (Row_Number() OVER (PARTITION BY classid Order by adddate DESC)) as Row_Num From g_News ) ntb Where Row_Num<=5
IF object_id('tb') IS NOT NULL DROP TABLE TB CREATE TABLE TB(id INT IDENTITY(1,1) PRIMARY KEY,item CHAR(1),times DATETIME, news NVARCHAR(50)) INSERT INTO dbo.TB SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GFDSGH' UNION ALL SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'GRJEKGHY' UNION ALL SELECT 'A',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'HYRTDJIOVFC' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'34R54FDSA' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'BVGFXBNH' UNION ALL SELECT 'B',DATEADD(DAY,ABS(CHECKSUM(NEWID()))%365,'2011-01-01'),'CVXZFGR' --创建测试数据 SELECT id , item , times , news FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY item ORDER BY times DESC ) AS rowid , * FROM dbo.TB ) a WHERE rowid <= 2--前几条这里就写几
Select * From
(
Select *, (Row_Number() OVER (PARTITION BY classid Order by adddate DESC)) as Row_Num From g_News
) ntb Where Row_Num<=5