select o.*, case when st.f is null then o.f else st.f end as f, case when st.g is null then o.g else st.g end as g from o left join ( select mid, max(if(type='f',value,0)) as f, max(if(type='g',value,0)) as g from st where not exists(select 1 from st s where s.mid=st.mid and s.type=st.type and s.time>st.time) group by mid ) as st on o.mid=st.mid
加个IFNULL函数。 select o.*, IFNULL((select `value` from st where mid=o.mid and type='f' order by `time` desc limit 1),0) as f, IFNULL((select `value` from st where mid=o.mid and type='g' order by `time` desc limit 1),0) as g from o
应该不会有问题,报错还是数据不对? 另外,还可以试试下面这个: select o.*, case when st.f is null then o.f else st.f end as f, case when st.g is null then o.g else st.g end as g from o left join ( select mid, max(if(type='f',value,0)) as f, max(if(type='g',value,0)) as g from st where time=(select max(time) from st s where s.mid=st.mid and s.type=st.type) group by mid ) as st on o.mid=st.mid
o.*,
case when st.f is null then o.f else st.f end as f,
case when st.g is null then o.g else st.g end as g
from
o
left join
(
select
mid,
max(if(type='f',value,0)) as f,
max(if(type='g',value,0)) as g
from
st
where
not exists(select 1 from st s where s.mid=st.mid and s.type=st.type and s.time>st.time)
group by mid
) as st
on o.mid=st.mid
select o.*,
IFNULL((select `value` from st where mid=o.mid and type='f' order by `time` desc limit 1),0) as f,
IFNULL((select `value` from st where mid=o.mid and type='g' order by `time` desc limit 1),0) as g
from o
acmain_chm 这个意思不对,当 st里能select出值的时候就显示 st里的值,但是没有,就显示 o的 f g的值。
另外,还可以试试下面这个:
select
o.*,
case when st.f is null then o.f else st.f end as f,
case when st.g is null then o.g else st.g end as g
from
o
left join
(
select
mid,
max(if(type='f',value,0)) as f,
max(if(type='g',value,0)) as g
from
st
where
time=(select max(time) from st s where s.mid=st.mid and s.type=st.type)
group by mid
) as st
on o.mid=st.mid