分组查询24小时之内的实时车流量数据

女爷i 2021-12-12 15:01 337阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doejE5OTUxMQ_size_16_color_FFFFFF_t_70][]

以当前时间为节点，查询前24小时之内的实时车流量进出数据。

SELECT
    			a. HOUR HOUR,
    			ifnull(b.count, 0)enterVehicleNum,
    			ifnull(c.count, 0)leaveVehicleNum
    		FROM
    			( SELECT DATE_FORMAT( DATE_ADD( date_sub(NOW(), INTERVAL 1 DAY), INTERVAL xc - 1 HOUR ), '%Y-%m-%d %H' )AS HOUR
    				FROM
    					( SELECT @xi :=@xi + 1 AS xc FROM
    							( SELECT 1 UNION SELECT 2 UNION SELECT 3 )xc1,
    							( SELECT 1 UNION SELECT 2 UNION SELECT 3 )xc2,
    							( SELECT 1 UNION SELECT 2 UNION SELECT 3 )xc3,
    							(SELECT @xi := 0)xc0
    					)xcxc
    			)a
    		LEFT JOIN(
    			SELECT
    				date_format(in_time, '%Y-%m-%d %H')HOUR,
    				count(in_time)count
    			FROM
    				t_ic_vehicle_record
    			WHERE
    				delete_flag = '0'
    			AND date_format(in_time, '%Y-%m-%d %H')>= date_sub(NOW(), INTERVAL 1 DAY)
    			GROUP BY
    				date_format(in_time, '%Y-%m-%d %H'),
    				HOUR
    		)b ON a. HOUR = b. HOUR
    		LEFT JOIN(
    			SELECT
    				date_format(pass_time, '%Y-%m-%d %H')HOUR,
    				count(pass_time)count
    			FROM
    				t_ic_vehicle_record
    			WHERE
    				delete_flag = '0'
    			AND date_format(pass_time, '%Y-%m-%d %H')>= date_sub(NOW(), INTERVAL 1 DAY)
    			GROUP BY
    				date_format(pass_time, '%Y-%m-%d %H'),
    				HOUR
    		)c ON a. HOUR = c. HOUR
    		ORDER BY
    			HOUR

关键点就是先把24小时这个表制作出来，其他的在查

打印结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doejE5OTUxMQ_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doejE5OTUxMQ_size_16_color_FFFFFF_t_70]: /images/20211210/ce712ab6dc754105bd7456ff2bb656b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doejE5OTUxMQ_size_16_color_FFFFFF_t_70 1]: /images/20211210/8bb268d1bf594b25a066351d94374216.png