用一条sql取出每个分类的前2条记录

 

--用一条sql 取出每个分类的前2条记录

--按公司分组, 查每个分组中最新的前2条记录 

 

SELECT FID, FAccount, FCompanyID
FROM dbo.TAccounts AS a
WHERE FID IN ( 
SELECT TOP(2) FID FROM dbo.TAccounts 
WHERE FCompanyID=a.FCompanyID  
ORDER BY FRegisterDate DESC 
)
ORDER BY a.FCompanyID DESC ,a.FID

 

--使用ROW_NUMBER() 进行排位分组
SELECT FID, FAccount, FCompanyID
FROM ( 
SELECT 
 rowNum = ROW_NUMBER() OVER (PARTITION BY FCompanyID ORDER BY FRegisterDate DESC ),* 
FROM dbo.TAccounts 
) AS a
WHERE a.rowNum <= 2
ORDER BY a.FCompanyID DESC ,a.FID

 

--使用APPLY
SELECT DISTINCT b.FID, b.FAccount, b.FCompanyID
FROM dbo.TAccounts AS a
CROSS APPLY( 
SELECT TOP(2) * FROM dbo.TAccounts 
WHERE a.FCompanyID = FCompanyID ORDER BY FRegisterDate DESC
) AS b
ORDER BY b.FCompanyID DESC ,b.FID