我有MySQL查询,用于将数据添加到表中reg_data会话=早晨,该查询正在运行......对于运行此查询数据,应每天定期添加到表中。由于使用了当前日期功能。但是,如果我将其更改为查看该查询运行不佳。这是我的原始代码
create into velocity (date,run,velocity)
select
DATE_SUB(CURDATE(),INTERVAL 1 DAY),
((select anem_reading from reg_data where date=CURDATE() and session='morning')-(select anem_reading from reg_data where date=DATE_SUB(CURDATE(),INTERVAL 1 DAY) and session='morning')) as run ,
round((((select anem_reading from reg_data where date=CURDATE() and session='morning')-(select anem_reading from reg_data where date= DATE_SUB(CURDATE(),INTERVAL 1 DAY) and session='morning')) /24 ),1) as velocity
from reg_data
where
session='morning'
and date=CURDATE();
我改变它创建视图
create view velocity as (date,run,velocity)
select
DATE_SUB(DATE(),INTERVAL 1 DAY),
((select anem_reading from reg_data where date=DATE() and session='morning')-(select anem_reading from reg_data where date=DATE_SUB(DATE(),INTERVAL 1 DAY) and session='morning')) as run ,
round((((select anem_reading from reg_data where date=DATE() and session='morning')-(select anem_reading from reg_data where date= DATE_SUB(DATE(),INTERVAL 1 DAY) and session='morning')) /24 ),1) as velocity
from reg_data
where
session='morning'
and date=DATE();
你能帮我这个查询运行不好吗,我想从不使用当天选项的表的现有数据运行reg_data视图。
试试这个,看看它是否是一个你想显示每天所有日期、运行、速度的视图
create view velocity as
select
DATE_SUB(r.date,INTERVAL 1 DAY) as date,
((select anem_reading from reg_data where date=r.date and session='morning')-(select anem_reading from reg_data where date=DATE_SUB(r.date,INTERVAL 1 DAY) and session='morning')) as run ,
round((((select anem_reading from reg_data where date=r.date and session='morning')-(select anem_reading from reg_data where date= DATE_SUB(r.date,INTERVAL 1 DAY) and session='morning')) /24 ),1) as velocity
from reg_data r
WHERE session='morning';
这是SQL小提琴