我有以下 SQL,它没有按照我想象的那样工作。
SET @runtot:=0;
select
q1.week,
(@runtot := @runtot + cast(q1.flying_hours as SIGNED)) as flying_hours,
q1.flying_hours as weekly_hours
from
(
select
date_format(d.date,"%x%v") as week,
cast(ceil(sum(fh.engine_minutes)/60) as SIGNED) as flying_hours
from dates d
left join flyinghours fh on date_format(fh.flight_start, "%x%v") = date_format(d.date,"%x%v")
where d.`date` >= "2019-01-01"
and d.date <= date_add(now(), interval 1 month)
group by date_format(d.date,"%x%v")
) q1
limit 20
结果是
"week" "flying_hours" "weekly_hours"
"201901" N N
"201902" N N
"201903" N N
"201904" N N
"201905" N "20"
"201906" N "9"
"201907" N N
"201908" N N
"201909" N N
"201910" N N
"201911" N N
"201912" N N
"201913" N N
"201914" N N
"201915" N N
"201916" N N
"201917" N "7"
"201918" N "39"
"201919" N "93"
"201920" N "9"
但是,我将flying_hours替换为它工作的子查询中的硬编码交互器。
SET @runtot:=0;
select
q1.week,
(@runtot := @runtot + cast(q1.flying_hours as SIGNED)) as flying_hours,
q1.flying_hours as weekly_hours
from
(
select
date_format(d.date,"%x%v") as week,
5 as flying_hours
from dates d
left join flyinghours fh on date_format(fh.flight_start, "%x%v") = date_format(d.date,"%x%v")
where d.`date` >= "2019-01-01"
and d.date <= date_add(now(), interval 1 month)
group by date_format(d.date,"%x%v")
) q1
limit 20
"week" "flying_hours" "weekly_hours"
"201901" "5" "5"
"201902" "10" "5"
"201903" "15" "5"
"201904" "20" "5"
"201905" "25" "5"
"201906" "30" "5"
"201907" "35" "5"
"201908" "40" "5"
"201909" "45" "5"
"201910" "50" "5"
"201911" "55" "5"
"201912" "60" "5"
"201913" "65" "5"
"201914" "70" "5"
"201915" "75" "5"
"201916" "80" "5"
"201917" "85" "5"
"201918" "90" "5"
"201919" "95" "5"
"201920" "100" "5"
问题是因为某些值为空。当我使用 ifnull(( 替换为零时,它都可以工作。
SET @runtot:=0;
select
q1.week,
@runtot := @runtot + ifnull(q1.flying_hours,0) as flying_hours,
q1.flying_hours as weekly_hours
from
(
select
date_format(d.date,"%x%v") as week,
cast(ceil(sum(fh.engine_minutes)/60) as SIGNED) as flying_hours
from dates d
left join flyinghours fh on date_format(fh.flight_start, "%x%v") = date_format(d.date,"%x%v")
where d.`date` >= "2019-01-01"
and d.date <= date_add(now(), interval 1 month)
group by date_format(d.date,"%x%v")
) q1
limit 20
"week" "flying_hours" "weekly_hours"
"201901" "0" N
"201902" "0" N
"201903" "0" N
"201904" "0" N
"201905" "20" "20"
"201906" "29" "9"
"201907" "29" N
"201908" "29" N
"201909" "29" N
"201910" "29" N
"201911" "29" N
"201912" "29" N
"201913" "29" N
"201914" "29" N
"201915" "29" N
"201916" "29" N
"201917" "36" "7"
"201918" "75" "39"
"201919" "168" "93"
"201920" "177" "9"