mysql查询分组前5个

SELECT
	*
FROM
	(
		SELECT
			s.num,
			(
				@row_number := (
					CASE
					WHEN @create_date_no = s.create_date THEN
						@row_number + 1
					ELSE
						1
					END
				)
			) row_num,
			@create_date_no := s.create_date create_date,
			s.shop_id
		FROM
			(
				SELECT
					count(1) num,
					DATE_FORMAT(o.create_date, '%Y%m%d') create_date,
					o.shop_id
				FROM
					order o
				GROUP BY
					DATE_FORMAT(o.create_date, '%Y%m%d'),
					o.shop_id
				ORDER BY
					DATE_FORMAT(o.create_date, '%Y%m%d') DESC,
					num DESC
			) s,
			(
				SELECT
					@create_date_no := 0 ,@row_number := 0
			) t
	) b
WHERE
	b.row_num < 6