原创

MySQL-代码-对两个表的合计数据进行二次汇总合计，并求ROUND百分比

以下是计算线上课程和线下课程的总体的完成率

EXPLAIN SELECT
		a.id,
		ROUND(
		CASE
		ifnull( sum(totalCourseCount4Calc ), 0 ) 
		WHEN 0 THEN 0 
  ELSE  ifnull(sum( totalFinishCourseCount4Calc ), 0 ) * 100 / sum( totalCourseCount4Calc ) 
		END,
		2 
		) AS courseFinishRatePercentage
		from (
				SELECT
				i.id,
				count( ( s.course_status = 2 OR NULL ) ) totalFinishCourseCount4Calc,
				count( 1 ) totalCourseCount4Calc
			FROM
				edu_train_implement i
				INNER JOIN edu_user_online_course_status s ON s.implement_id = i.id AND s.is_delete = i.is_delete
			WHERE i.id in (7,495) GROUP BY i.id
				
				union all
				
				SELECT
				i.id,
				count( ( s.course_status = 2 OR NULL ) ) totalFinishCourseCount4Calc,
				count( 1 ) totalCourseCount4Calc
			FROM
				edu_train_implement i
				INNER JOIN edu_user_offline_course_status s ON s.implement_id = i.id AND s.is_delete = i.is_delete
			WHERE i.id in (15,495) GROUP BY i.id
		) a GROUP BY a.id
							 
							 
			7	1	1
			495	0	14
			15	0	4
			495	0	7
			
			注意要加索引
			ALTER TABLE `edu_user_online_course_status` 
			ADD INDEX `idx_implement`(`implement_id`) USING BTREE;

			ALTER TABLE `edu_user_offline_course_status` 
			ADD INDEX `idx_implement`(`implement_id`) USING BTREE;

正文到此结束

所属分类：数据库

本文标签： MySQL
本文链接： https://code.jiangjiesheng.cn/article/275
版权声明： 本文由小江同学原创发布，转载请先联系本站长，谢谢。

MySQL-代码-对两个表的合计数据进行二次汇总合计，并求ROUND百分比

热门推荐

相关文章

说给你听

本文目录

标签云

近期评论

网站信息