我有一个临时表a
"date" "value"
"2016-09-09" 0.0533
"2016-09-12" 0.0552
"2016-09-14" 0.0567
"2016-09-15" 0.0537
"2016-09-19" 0.0529
"2016-09-19" 0.0506
"2016-09-19" 0.0525
"2016-09-20" 0.0517
"2016-09-20" 0.0534
还有一个临时的桌子b
,让我每周平均每周六
"date" "avgValue"
"2016-09-10" 0.0533
"2016-09-17" 0.0552
"2016-09-24" 0.0522
日期几乎从不排队,但有时可能会。我想做一个完整的外部连接,但 mysql 不允许这样做。所以我一直在尝试各种工会和加入,在过去的 4 个小时里,问题一直在踢我的屁股。我想要的表格看起来像这样
"date" "value" "avgValue"
"2016-09-09" 0.0533 NULL
"2016-09-10" NULL 0.0533
"2016-09-12" 0.0552 NULL
"2016-09-14" 0.0567 NULL
"2016-09-15" 0.0537 NULL
"2016-09-17" NULL 0.0552
"2016-09-19" 0.0529 NULL
"2016-09-19" 0.0506 NULL
"2016-09-19" 0.0525 NULL
"2016-09-20" 0.0517 NULL
"2016-09-20" 0.0534 NULL
"2016-09-24" NULL 0.0522
到目前为止,我有一个什么也没做。
select *
from a
left join b
on b.date = a.date
union
select *
from a
right join b
on b.date = a.date;
我建议使用union all
/group by
方法:
select date, max(value) as value, max(argvalue) as argvalue
from ((select date, value asvalue, NULL as argvalue from a) union all
(select date, NULL, argvalue from b)
) ab
group by date;