mysql - 更改表以查看 - 表使用日期功能

Question

我有 MySQL 查询，用于添加数据数据添加到 reg_data 表和 session=morning，该查询已运行......为了运行此查询，数据应每天定期添加到表中......由于使用当前日期函数。但如果我更改它以查看该查询运行得不好。这是我原来的代码

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();

我改变它创建 View

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 表的现有数据运行这个 View ，而不是使用当天选项。

Answer 1

尝试一下，看看它是否是您想要显示每天的所有日期、运行、速度的 View

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';

这是sqlFiddle