MySQL with rollup

最新推荐文章于 2024-01-22 09:53:00 发布

qq_39028580

最新推荐文章于 2024-01-22 09:53:00 发布

阅读量415

点赞数

分类专栏： SQL相关文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_39028580/article/details/124746940

版权

SQL相关专栏收录该内容

3 篇文章 1 订阅

订阅专栏

近期在做一个小需求时，涉及一个小SQL，优化时用到了 with rollup

优化前：

SELECT
	type_name typeName,
	sum(num)value,
	(
		SELECT
			sum(num)
		FROM
			tbl_rssmc_pro_bigcustomer_domain_type_qps
		WHERE
			modify_date >= '2022-05-12 10:29:52'
		AND modify_date <= '2022-05-13 10:29:52'
	) total
FROM
	tbl_rssmc_pro_bigcustomer_domain_type_qps
WHERE
	modify_date >= '2022-05-12 10:29:52'
AND modify_date <= '2022-05-13 10:29:52'
GROUP BY
	type_id,
	type_name
ORDER BY
	sum(num) DESC
LIMIT 3

优化后：

SELECT
	*
FROM
	(
		SELECT
			COALESCE (type_name, 'QPS总量') typeName,
			sum(num) value
		FROM
			tbl_rssmc_pro_bigcustomer_domain_type_qps
		WHERE
			modify_date >= '2022-05-12 10:29:52'
		AND modify_date <= '2022-05-13 10:29:52'
		GROUP BY
			type_name WITH ROLLUP
	) a
ORDER BY
	a.value DESC
LIMIT 4

总结：

（1）WITH ROLLUP

用来在分组统计数据的基础上再进行统计汇总，不能与order by、limit等一起使用

（2）COALESCE (）与IFNULL() 类似