还是刚刚的问题,忽略了一个重要的地方,两张表的hour字段有相同的地方,就不重复显示,可能表达不清楚,看下面
两张表 类型一样
table1:hour count
1 20
2 30
3 40table2:hour count
2 25
3 35
4 45我像要的查询结果格式如下
hour t1_count t2_count
1 20 0
2 30 25
3 40 35
4 0 45
请解答,谢谢
两张表 类型一样
table1:hour count
1 20
2 30
3 40table2:hour count
2 25
3 35
4 45我像要的查询结果格式如下
hour t1_count t2_count
1 20 0
2 30 25
3 40 35
4 0 45
请解答,谢谢
from tb1 A left join tb2 B on A.hour=B.housr
union
select *
from tb1 A right join tb2 B on A.hour=B.housr
create table t1(hour int,count int);
create table t2(hour int,count int);
insert into t1 values(1,20),(2,30),(3,40);
insert into t2 values(2,25),(3,35),(4,45);select t1.hour,t1.count ,0 from t1 where not exists(select 1 from t2 where t2.hour=t1.hour)
union all
select t1.hour,t1.count ,t2.count from t1 join t2 on t1.hour=t2.hour
union all
select t2.hour,0 ,t2.count from t2 where not exists(select 1 from t1 where t2.hour=t1.hour)
select hour,sum(t1_count) as t1_count,sum(t2_count) as t2_count
from(
select hour,`count` as t1_count,0 as t2_count From table1
union all
select hour,0,`count` from table2
) t
group by hour方法二:
select a.hour,a.count as t1_count,b.count as t2_count
from table1 a left join table2 b on a.hour=b.hour
union all
select b.hour,a.count as t1_count,b.count as t2_count
from table1 a Right join table2 b on a.hour=b.hour
where a.hour is null各有优缺点。
select a.hour,a.count as t1_count,b.count as t2_count
from table1 a left join table2 b on a.hour=b.hour
union all
select b.hour,a.count as t1_count,b.count as t2_count
from table1 a Right join table2 b on a.hour=b.hour
where a.hour is null