MySQL拼接表

外连接两表按照年份统计五年的建材费和业务费，并将统计的五年的表横向拼接

代码：

select a.year,a.jccost,a.ywcost,b.year,b.jccost,b.ywcost,c.year,c.jccost,c.ywcost,d.year,d.jccost,d.ywcost,e.year,e.jccost,e.ywcost
from (
select CONCAT(2021,'年') year,
SUM(IF(b.name='建材部',a.mount,0.00)) as jccost,
SUM(IF(b.name='业务部',a.mount,0.00)) as ywcost
FROM a 
LEFT JOIN b on a.custom4=b.ordercode
WHERE YEAR(a.date)=2021
)a
LEFT JOIN(
SELECT CONCAT(2021-1,'年') year,
SUM(IF(b.name='建材部',a.mount,0.00)) as jccost,
SUM(IF(b.name='业务部',a.amount,0.00)) as ywcost
FROM a 
LEFT JOIN b on a.custom4=b.ordercode
WHERE YEAR(a.date)=2021-1
)b
on 1=1
LEFT JOIN(
SELECT CONCAT(2021-2,'年') year,
SUM(IF(b.name='建材部',a.mount,0.00)) as jccost,
SUM(IF(b.name='业务部',a.mount,0.00)) as ywcost
FROM a 
LEFT JOIN b on a.custom4=b.ordercode
WHERE YEAR(a.date)=2021-2
)c
on 1=1
LEFT JOIN(
SELECT CONCAT(2021-3,'年') year,
SUM(IF(b.name='建材部',a.mount,0.00)) as jccost,
SUM(IF(b.name='业务部',a.mount,0.00)) as ywcost
FROM a 
LEFT JOIN b on a.custom4=b.ordercode
WHERE YEAR(a.date)=2021-3
)d
on 1=1
LEFT JOIN(
SELECT CONCAT(2021-4,'年') year,
SUM(IF(b.name='建材部',a.mount,0.00)) as jccost,
SUM(IF(b.name='业务部',a.mount,0.00)) as ywcost
FROM a 
LEFT JOIN b on a.custom4=b.ordercode
WHERE YEAR(a.date)=2021-4
)e
on 1=1

结果：