Mysql 语句

查询一天中 每个小时的数据

SELECT timetable.hour Hour, ifnull(sumtable.room_temperature, 0) room_temperature,ifnull(sumtable.room_humidity, 0) room_humidity, ifnull(sumtable.co2_concentration, 0) co2_concentration, ifnull(sumtable.illumination_intensity, 0) illumination_intensity,ifnull(sumtable.soil_temperature, 0) soil_temperature,ifnull(sumtable.soil_humidity, 0) soil_humidity FROM (SELECT 0 hour UNION ALL SELECT 2 hour UNION ALL SELECT 4 hour UNION ALL SELECT 6 hour UNION ALL SELECT 8 hour UNION ALL SELECT 10 hour UNION ALL SELECT 12 hour UNION ALL SELECT 14 hour UNION ALL SELECT 16 hour UNION ALL SELECT 18 hour UNION ALL SELECT 20 hour UNION ALL SELECT 22 hour UNION ALL SELECT 24 hour) timetable LEFT JOIN( SELECT
hour(A.created_date) hour,
A.room_temperature,
A.room_humidity,
A.co2_concentration,
A.illumination_intensity,
A.soil_temperature,
A.soil_humidity
FROM iot_greenhouse_realtime_data AS A
WHERE ABS(DATEDIFF(now(),A.created_date)) <=1 and (DATEDIFF(now(),A.created_date)) >0 AND greenhouse_code=‘A1’
GROUP BY date_format(A.created_date, ‘%Y%m%d-%H’), hour
) sumtable ON timetable.hour = sumtable.hour ORDER BY hour;

改变查询到的内容

SELECT case when DATE_FORMAT(A.created_date,’%Y-%m-%d’)=DATE_SUB(curdate(),INTERVAL 1 DAY) then ‘昨天’ when DATE_FORMAT(A.created_date,’%Y-%m-%d’)=DATE_SUB(curdate(),INTERVAL 2 DAY) then ‘前天’ else DATE_FORMAT(A.created_date,’%m-%d’) end weight, SUM(A.weight) AS weight FROM iot_fertilization_log AS A
WHERE ABS(DATEDIFF(now(),A.created_date)) <=7 and (DATEDIFF(now(),A.created_date)) >0 and A.greenhouse_code=‘A1’
GROUP BY DATE_FORMAT(A.created_date,’%Y-%m-%d’)

mysql 获得最新的数据并且去除重复

商品表 product

字段 id、 product_code、product_name、create_date

select * from (select *,product_code as code from product order by create_date desc) t group by code;