我有一个表格,其中有三个字段:
Id, Creation Time, Fuel Level
每两分钟我们都会获得数据并将其插入到数据库中。为了生成燃料信用/借记报表,我想要获取当天的起始(一天的开始)和结束(一天的结束)燃料水平。有人能帮忙编写一个查询来生成这个报告吗?
搜索参数将是日期范围。
Id = 10;创建时间=2019-02-15 16:32:59;燃料水平=20
我在这里创建了一个示例模式 http://sqlfiddle.com/#!2/76dd5
我有一个表格,其中有三个字段:
Id, Creation Time, Fuel Level
首先,如果您需要查询每辆车每天的燃料消耗量变化,请使用以下SQL语句:
SELECT trip_range.dt
, trip_range.vehicle_id
, st.fuel_content as start_fuel_content
, en.fuel_content as end_fuel_content
, en.fuel_content - st.fuel_content as fuel_change
FROM (
SELECT tp.vehicle_id, DATE(tp.creation_time) dt
, MIN(tp.creation_time) start_time
, MAX(tp.creation_time) end_time
FROM trip_parameters tp
GROUP BY tp.vehicle_id, DATE(tp.creation_time)
) as trip_range
JOIN trip_parameters st
ON st.vehicle_id = trip_range.vehicle_id
AND st.creation_time = trip_range.start_time
JOIN trip_parameters en
ON en.vehicle_id = trip_range.vehicle_id
AND en.creation_time = trip_range.end_time
WHERE trip_range.dt BETWEEN '2012-11-08' AND '2012-11-09'
SELECT dt, SUM(fuel_change) as fuel_change
FROM (
SELECT trip_range.dt
, en.fuel_content - st.fuel_content as fuel_change
FROM (
SELECT tp.vehicle_id, DATE(tp.creation_time) dt
, MIN(tp.creation_time) start_time
, MAX(tp.creation_time) end_time
FROM trip_parameters tp
GROUP BY tp.vehicle_id, DATE(tp.creation_time)
) as trip_range
JOIN trip_parameters st
ON st.vehicle_id = trip_range.vehicle_id
AND st.creation_time = trip_range.start_time
JOIN trip_parameters en
ON en.vehicle_id = trip_range.vehicle_id
AND en.creation_time = trip_range.end_time
WHERE trip_range.dt BETWEEN '2012-11-08' AND '2012-11-09'
) change_by_vehicle
GROUP BY 1
请尝试以下:
SELECT Id, CreationTime, FuelLevel
FROM MYTABLE
WHERE DATE(CreationTime) = CURDATE();
此代码获取CreationTime
的日期部分并与当前日期进行比较,因此返回所有今天创建的记录。
DATE FUEL_CONTENT_AT_START_OF_DAY FUEL_CONTENT_AT_END_OF_DAY
Nov, 08 2012 4.6 3.6
Nov, 09 2012 11.6 5.6
Query
SELECT
DATE( startofday.creation_time) AS date,
startofday.fuel_content AS fuel_content_at_start_of_day,
(SELECT fuel_content FROM trip_parameters WHERE trip_paramid = max(endofday.trip_paramid) ) AS fuel_content_at_end_of_day
FROM
trip_parameters startofday
INNER JOIN trip_parameters endofday ON ( DATE( endofday.creation_time ) = DATE( startofday.creation_time) )
WHERE
DATE( startofday.creation_time) BETWEEN '2012-11-08' AND '2012-11-09'
GROUP BY
startofday.TRIP_PARAMID
HAVING
min( endofday.trip_paramid ) = startofday.trip_paramid