mysql 取每个分类的前N条数据。

现有一个这样的需求：在一张表中取有100个分类下的1000条数据。现要取每个分类下的前4条数据。

表结构如下：

CREATE TABLE `apply` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `isValid` varchar(2) DEFAULT NULL COMMENT '逻辑删除标识1正常0删除',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `updateTime` datetime DEFAULT NULL COMMENT '更新时间',
  `applyTypeId` varchar(32) DEFAULT NULL COMMENT '应用分类ID',
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

取前N条之前先看一个根据时间排序的数据。

SELECT  count(ay.createTime),ay.applyTypeId,ay.id,ay.`name` from 
 apply ay
left JOIN 
apply AS b 
on b.applyTypeId = ay.applyTypeId
and b.createTime >= ay.createTime
where ay.isValid=1 and b.isValid=1
group by ay.applyTypeId,ay.createTime 

查询结果如下：

 

现取某个分类的前4条

SELECT
ay.applyTypeId,ay.id,ay.`name`
FROM
apply ay 
WHERE
 ay.isValid = 1
AND 
 (
	SELECT
		count(1)
	FROM
		apply AS b
	WHERE
		b.applyTypeId = ay.applyTypeId
 and b.isValid=1
	AND b.createTime >= ay.createTime
) <=4
ORDER BY
ay.applyTypeId

结果如下：

 

---

另外当发现分类太多时，只选择最多2个分类有数据且，选取其中4条数据；

SELECT
ay.applyTypeId,ay.id,ay.`name`
FROM
apply ay 

WHERE
 ay.isValid = 1

AND ay.applyTypeId IN (
	SELECT
		c.id
	FROM
		(
			SELECT
				t.id
			FROM
				apply_type t
			WHERE
				t.isValid = 1
			AND EXISTS (
				SELECT
					1
				FROM
					apply
				WHERE
					apply.applyTypeId = t.id
				AND apply.isValid = 1
			)
			ORDER BY
				LENGTH(t.sort) ASC,
				t.sort ASC
			LIMIT 2
		) AS c
)
AND 
 (
	SELECT
		count(1)
	FROM
		apply AS b
	WHERE
		b.applyTypeId = ay.applyTypeId
 and b.isValid=1
	AND b.createTime >= ay.createTime
) <=4
ORDER BY
ay.applyTypeId

#此处需要先查询 分类表 文中没有需要自己创建