sql合并列数（取并集）

最新推荐文章于 2023-11-21 17:25:48 发布

一个人的幽默

最新推荐文章于 2023-11-21 17:25:48 发布

阅读量5.2k

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_39578388/article/details/82692563

版权

sql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

要求

现在有三个表，分别是年、月、数量三列，现在想整合成一个结果：
四列，分别是年-月、数量a、数量b、数量c
三个表如下（举个例子，非真实数据）
year month sum
2017 8 100
2017 9 150
具体的结果如下
year-month sumA sumB sumC
2017-6 0 100 0
2017-8 100 50 0

ps：就是相同年月的为一行，若表没有这个年月的数据补上0或者空

select concat(pyear,'-',pmonth)pmonth,SUM(psum1) AS applyWat,SUM(psum2) AS planWat,SUM(psum3) AS actualWat 
from (
            (SELECT pyear ,pmonth,psum as psum1,'0' as psum2,'0' as psum3
                from zssl_planwater  where del_flag='0')
            UNION
            (SELECT atime ,amonth,'0' as psum1,asum as psum2,'0' as psum3
                from zssl_Applywater   where del_flag='0')
            UNION
            (SELECT pyear ,pmonth,'0' as psum1,'0' as psum2,psum as psum3
                from zssl_actual  where del_flag='0')
        ) as f
        where pyear is not null and pmonth  is not null

        GROUP BY concat(pyear,'-',pmonth)
        order by  concat(pyear,'-',pmonth) desc