多个查询结果如何用一个语句合并成一个表。

Question

表一语句如下

select mode,sum(pv) as 2020年8月1日
from
(SELECT a.pv,a.name,b.mode,b.id,a.mytime,a.new_on_the_day 
from 2020_08_data a,charging_id_information b 
where a.pid = b.id
and a.mytime = '2020-08-01')a
GROUP BY mode
ORDER BY mode DESC

表二语句如下

select mode,sum(pv) as 2020年8月2日
from
(SELECT a.pv,a.name,b.mode,b.id,a.mytime,a.new_on_the_day 
from 2020_08_data a,charging_id_information b 
where a.pid = b.id
and a.mytime = '2020-08-02')a
GROUP BY mode
ORDER BY mode DESC

表三语句如下

select mode,sum(pv) as 2020年8月3日
from
(SELECT a.pv,a.name,b.mode,b.id,a.mytime,a.new_on_the_day 
from 2020_08_data a,charging_id_information b 
where a.pid = b.id
and a.mytime = '2020-08-03')a
GROUP BY mode
ORDER BY mode DESC

想问下各路大神怎么写能得出表四 谢谢各位

Answer 1

select 
a.mode,
sum(case when b.mytime = '2020-08-01' then ifnull(b.pv,0) else 0 end) 2020年8月1日,
sum(case when b.mytime = '2020-08-02' then ifnull(b.pv,0) else 0 end) 2020年8月2日,
sum(case when b.mytime = '2020-08-03' then ifnull(b.pv,0) else 0 end) 2020年8月3日
from 
charging_id_information a
left join
2020_08_data b
on a.id = b.id
group by a.mode
order by a.mode desc;