mysql - 合并两个表的数据

Question

我有 2 张 table

table1 和它有列

 id 
 sdate
 type 

类型列可以有 2 个值事件或计划我正在使用此查询来获取总事件和计划的结果

 select date_format(sdate, '%m-%d-%Y') as sdate, sum( type ='event') as tevent , sum(type='schedule') as tschedule from table1 where sid ='1' group by (sdate);

表 2 有这些列

 id
 title
 dtime

为了使它们都类似于联合中的使用，我做了这样的事情

 select date_format(dtime, '%m-%d-%Y') as sdate ,0 as tevent,0 as tschedule,count(id) as tlog from table2 where sid =1  group by (sdate) ;

我有点困惑，如何从两个表中获取数据，如果日期相同，它应该在一列中显示数据。

Answer 1

试试这个(包括对原始查询的一些更正):

select sdate, sum(tevent) as tevent, 
sum(tschedule) as tschedule, sum(tlog) as tlog
from (
select date_format(sdate, '%m-%d-%Y') as sdate, 
sum(type='event') as tevent , 
sum(type='schedule') as tschedule,
0 as tlog from table1
group by sdate
union
select date_format(dtime, '%m-%d-%Y') as sdate ,
0 as tevent,0 as tschedule,
count(id) as tlog from table2 
group by sdate 
) s group by sdate;