MySQL GROUP BY 分组取字段最大值

技术笔记 - 数据库 - MySQL

发布时间:2020-02-03 12:16:21

admin 于  2020-02-03 12:16:21 编辑

方法一(效率最高)

SELECT
	*
FROM
	test AS a
WHERE
	typeindex = (
		SELECT
			max(b.typeindex)
		FROM
			test AS b
		WHERE
			a.type = b.type
	);

方法二(效率次之)

SELECT
	a.*
FROM
	test a,
	(
		SELECT
			type,
			max(typeindex) typeindex
		FROM
			test
		GROUP BY
			type
	) b
WHERE
	a.type = b.type
AND a.typeindex = b.typeindex
ORDER BY
	a.type

方法三

SELECT
	a.*
FROM
	test a
INNER JOIN (
	SELECT
		type,
		max(typeindex) typeindex
	FROM
		test
	GROUP BY
		type
) b ON a.type = b.type
AND a.typeindex = b.typeindex
ORDER BY
	a.type

方法四(效率最低)

SELECT
	*
FROM
	(
		SELECT
			*, ROW_NUMBER () OVER (
				PARTITION BY type
				ORDER BY
					typeindex DESC
			) AS num
		FROM
			test
	) t
WHERE
	t.num = 1

转载声明:本站文章无特别说明,皆为原创,版权所有,转载请注明:Dy大叔的日常

转载自 MySQL GROUP BY 分组取字段最大值 | XDY.ME@Dy大叔的日常

XDY.ME@Dy大叔的日常