MySQL拼接表

最新推荐文章于 2024-08-19 09:28:34 发布

原创最新推荐文章于 2024-08-19 09:28:34 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

8 篇文章

订阅专栏

这段代码展示了如何使用SQL进行外连接操作，统计过去五年中每年的建材费和业务费。通过LEFT JOIN连接多个子查询，分别对应不同年份的数据，然后将结果横向拼接展示。每个子查询计算了特定年份内建材部和业务部的费用总和。

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

代码：

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

结果：