SQL分组查询每组前N条记录

场景：

场景：问答功能模块，问题列表要展示该问题下某种排序规则下前N条的回答。

---

描述

要求：问题下的答案以以下方式排序，先按top_order倒序，相同的按heat倒序，相同的按create_at倒序。

表结构如下：

CREATE TABLE `app_question_answer` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '回答用户ID',
  `model` varchar(50) NOT NULL DEFAULT '' COMMENT 'model',
  `question_id` int(11) NOT NULL DEFAULT '0' COMMENT '问题ID',
  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '答案',
  `gift_integral` int(11) NOT NULL DEFAULT '0' COMMENT '赠送积分',
  `create_at` bigint(20) NOT NULL DEFAULT '0' COMMENT '回答时间',
  `processed_at` bigint(20) NOT NULL DEFAULT '0' COMMENT '审核时间',
  `top_order` bigint(20) NOT NULL DEFAULT '0' COMMENT '置顶：时间戳，0表示未置顶，大于0表示置顶，值越大排越前',
  `like_count` int(11) NOT NULL DEFAULT '0' COMMENT '点赞数',
  `comment_count` int(11) NOT NULL DEFAULT '0' COMMENT '评论数',
  `heat` int(11) NOT NULL DEFAULT '0' COMMENT '热度',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态：未审核 审核通过 审核拒绝',
  `had_star` tinyint(1) NOT NULL DEFAULT '0' COMMENT '曾经精选过：0未被精选过 1曾经精选过',
  `disable` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否删除： 1删除 0未删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_question_user` (`user_id`,`question_id`),
  FULLTEXT KEY `fulltext_content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1088 DEFAULT CHARSET=utf8mb4 COMMENT='问题回答';

分析：

按照上述规则排序有多少条答案比当前记录的答案排名靠前，然后根据具体的多少（N）条+1就是N+1就是当前答案所在其问题下的排名。

方式1:

SELECT
	a.* 
FROM
	(
	SELECT
		a1.*,
		(
		SELECT
			count(*) + 1 
		FROM
			app_question_answer a2 
		WHERE
			a2.question_id = a1.question_id 
			AND (
				a2.top_order > a1.top_order 
				OR ( a2.top_order = a1.top_order AND a2.heat > a1.heat ) 
				OR ( a2.top_order = a1.top_order AND a2.heat = a1.heat AND a2.create_at > a1.create_at ) 
			) 
		) top 
	FROM
		app_question_answer a1 
	) a 
WHERE
	top <= 5 
	AND question_id IN ( xxx ) 
ORDER BY
	a.top_order DESC,
	a.heat DESC,
	a.create_at DESC,
	top DESC;

方式2:

SELECT
	a1.`id`,
	a1.`user_id`,
	a1.`model`,
	a1.`question_id`,
	a1.`content`,
	a1.`gift_integral`,
	a1.`create_at`,
	a1.`processed_at`,
	a1.`top_order`,
	a1.`like_count`,
	a1.`heat`,
	a1.`status`,
	a1.`had_star`,
	a1.`disable`,
	a1.`create_time`,
	a1.`update_time` 
FROM
	app_question_answer a1 
WHERE
	question_id IN ( xxx ) 
	AND DISABLE = 0 
	AND STATUS = 1 
	AND EXISTS (
	SELECT
		count(*) + 1 
	FROM
		app_question_answer a2 
	WHERE
		a2.question_id = a1.question_id 
		AND (
			a2.top_order > a1.top_order 
			OR ( a2.top_order = a1.top_order AND a2.heat > a1.heat ) 
			OR ( a2.top_order = a1.top_order AND a2.heat = a1.heat AND a2.create_at > a1.create_at ) 
		) 
	HAVING
		( count(*) + 1 ) <= 5 
	) 
ORDER BY
	a1.question_id DESC,
	a1.top_order DESC,
	a1.heat DESC,
	a1.create_at DESC

方式3:

SELECT
	a1.* 
FROM
	app_question_answer a1 
WHERE
	(
	SELECT
		count(*)+ 1 
	FROM
		app_question_answer a2 
	WHERE
		a2.question_id = a1.question_id 
		AND (
			a2.top_order > a1.top_order 
			OR ( a2.top_order = a1.top_order AND a2.heat > a1.heat ) 
			OR ( a2.top_order = a1.top_order AND a2.heat = a1.heat AND a2.create_at > a1.create_at ) 
		)) <= 5 
	AND question_id IN ( xxx ) 
ORDER BY
	a1.top_order DESC,
	a1.heat DESC,
	a1.create_at DESC