MySQL分组取top n

mysql查询时取前top n条数据

SELECT sheetDate, goodsId, goodsName, payOrderNum, actualTotalAmount FROM ( 
SELECT @row_number = 0, 
(@row_number := CASE WHEN @sheetDate = sheetDate THEN @row_number + 1 ELSE 1 END) AS num, 
@sheetDate:=sheetDate AS sheetDate, goodsId, goodsName, payOrderNum, actualTotalAmount 
FROM 
(
SELECT @row_number := 0, @sheetDate := NULL, sheetDate, goodsId, goodsName, payOrderNum, actualTotalAmount 
FROM t_goods_sale_daily_sheet WHERE sheetDate >= #{startDate} AND sheetDate <= #{endDate} and calendarType = #{calendarType} 
) X 
ORDER BY sheetDate DESC, payOrderNum DESC 
) T WHERE num <= 20

1.根据sheetDate , payOrderNum 倒序排序
2.查询时，下一行数据跟上一行的数据进行对比，此处对比的@sheetDate = sheetDate，若相同则@row_number + 1，（@row_number := 0, @sheetDate := NULL 要进行初始化，否则会出现首条数据进行比较时无数据可比）
3.取查询出来的结果前20条num <= 20
备注：联合索引 sheetDate，calendarType ；数据600万条，查询时间20秒；数据量较少的表可以使用该方式进行查询，最终未使用该方式进行查询，而是将逻辑放到server中。