SELECT
COUNT(*) AS `数量`,
subquery.total_count AS `总数`,
COUNT(*) * 100 / subquery.total_count AS `百分比`,
CASE
WHEN park_long < 900 THEN '15分钟以内'
WHEN park_long >= 900 AND park_long < 3600 THEN '15-60分钟'
WHEN park_long >= 3600 AND park_long < 10800 THEN '1-3小时'
WHEN park_long >= 10800 AND park_long < 21600 THEN '3-6小时'
WHEN park_long >= 21600 AND park_long < 43200 THEN '6-12小时'
WHEN park_long >= 43200 THEN '12小时以上'
ELSE '当天未出场'
END AS `停车时长分类`
FROM
park_parking_payment_list
JOIN (
SELECT COUNT(*) AS total_count
FROM park_parking_payment_list
WHERE in_time IS NOT NULL
AND out_time IS NOT NULL
AND date_format( report_time, '%y%m%d' ) = date_format( '${queryTime}', '%y%m%d' )
) AS subquery
WHERE
in_time IS NOT NULL
AND out_time IS NOT NULL
AND date_format( report_time, '%y%m%d' ) = date_format( '${queryTime}', '%y%m%d' )
GROUP BY `停车时长分类`, subquery.total_count;
MYSQL 统计停车时长百分比
于 2023-08-22 10:54:35 首次发布