sql小技巧之mysql关于百分比的查询

1.查询当前月相较于上月增长或者减少的百分比查询

查询语句结构如下：

SELECT
	count(1) total,
	COUNT( CASE WHEN MONTH ( create_time ) = MONTH ( CURRENT_DATE ) AND YEAR ( create_time ) = YEAR ( CURRENT_DATE ) THEN 1 END ) AS current_month_count,
	COUNT(
	CASE
			
			WHEN MONTH ( create_time ) = MONTH ( CURRENT_DATE - INTERVAL 1 MONTH ) 
			AND YEAR ( create_time ) = YEAR ( CURRENT_DATE - INTERVAL 1 MONTH ) THEN
				1 
			END 
			) AS last_month_count,
			ROUND(
				(
					COUNT( CASE WHEN MONTH ( create_time ) = MONTH ( CURRENT_DATE ) AND YEAR ( create_time ) = YEAR ( CURRENT_DATE ) THEN 1 END ) - COUNT(
					CASE
							
							WHEN MONTH ( create_time ) = MONTH ( CURRENT_DATE - INTERVAL 1 MONTH ) 
							AND YEAR ( create_time ) = YEAR ( CURRENT_DATE - INTERVAL 1 MONTH ) THEN
								1 
							END 
							) 
							) / NULLIF(
							COUNT(
							CASE
									
									WHEN MONTH ( create_time ) = MONTH ( CURRENT_DATE - INTERVAL 1 MONTH ) 
									AND YEAR ( create_time ) = YEAR ( CURRENT_DATE - INTERVAL 1 MONTH ) THEN
										1 
									END 
									),
									0 
								) * 100,
								2 
							) AS percentage_change 
					FROM
	robot;

2.关于某个字段的某一状态占全表百分比

SELECT 
    (COUNT(*) / (SELECT COUNT(*) FROM robot_task)) * 100 AS bigRate
FROM robot_task
WHERE status = 2;

好了，这期sql小技巧就结束了，感谢你的浏览。