MySQL 分组后取每组前N条数据（天猫项目）_mysql取分组后前四个数据-优快云博客

本文介绍了一种使用SQL从数据库中选取特定分组内的前几条记录的方法，并通过一个天猫项目的具体案例进行了解析。该方法涉及多表连接、子查询及分组排序等高级SQL技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL：

select * from emp as a where 3> (select count(*) from emp where deptno = a.deptno and sal < a.sal  )  order by a.deptno ,a.sal

解析SQL：

3>…

目的是获取前三条数据，可以多次修改

emp where deptno = a.deptno

目的是确定分组字段

sal < a.sal

排序条件

天猫项目目sql代码：

SELECT * FROM
	( SELECT
		c.id,c.`name`,p.id AS pid,p.sub_title,p.promote_price,pi.id as piid 
		FROM  t_category AS c
		LEFT JOIN t_product AS p ON c.id = p.category_id
		LEFT JOIN ( SELECT max( pi.id ) AS id,pi.product_id AS pid 
		FROM t_product_image pi 
		WHERE pi.type = 'type_single' 
		GROUP BY pi.product_id 
		) AS i ON i.pid = p.id
		LEFT JOIN t_product_image pi ON pi.id = i.id ) s 
		WHERE 5 > (
	SELECT count(*) 
	FROM
		(SELECT c.id,c.`name`,p.id AS pid,p.sub_title,p.promote_price,pi.id as piid 
	FROM t_category AS c
		LEFT JOIN t_product AS p ON c.id = p.category_id
		LEFT JOIN (
		SELECT max( pi.id ) AS id,pi.product_id AS pid 
		FROM
			t_product_image pi 
		WHERE
			pi.type = 'type_single' 
		GROUP BY
			pi.product_id 
		) AS i ON i.pid = p.id
		LEFT JOIN t_product_image pi ON pi.id = i.id 
			) a 
		WHERE
			a.id = s.id 
			AND a.pid < s.pid 
		)
	ORDER BY s.id,s.pid