orcale数据列补位操作

最新推荐文章于 2022-09-09 06:05:22 发布

liuhongJAVAEn

最新推荐文章于 2022-09-09 06:05:22 发布

阅读量885

点赞数

分类专栏：数据库与SQL 文章标签： date

本文链接：https://blog.csdn.net/w19861201/article/details/6662857

版权

数据库与SQL 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

首先将两上相同地表放在一起，在前一个表里面预留列位，同时后一个表也要预留列位，这样整体查询出来地数据都会相应补位。
    select a.dwjc,NVL(sum(a.jcz), 0) jcz,
               NVL(sum(a.jsq), 0) jsq,
               NVL(sum(a.jdw), 0) jdw,
               NVL(sum(a.jxx), 0) jxx,
               NVL(sum(a.gj), 0) gj,
               NVL(sum(a.sj), 0) sj,
               NVL(sum(a.dq), 0) dq,
               nvl(sum(a.jcz + a.jsq + a.jdw + a.jxx), 0) ct

     from(

          select b.dwjc,
                       nvl((CASE
                             WHEN lb = 1 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where lb = a.lb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as jcz,
                       nvl((CASE
                             WHEN lb = 2 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where lb = a.lb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as jsq,
                       nvl((CASE
                             WHEN lb = 3 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where lb = a.lb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as jdw,
                       nvl((CASE
                             WHEN lb = 4 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where lb = a.lb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as jxx,
                       nvl(0, 0) gj,
                       nvl(0, 0) sj,
                       nvl(0, 0) dq
                  from (select *
                          From xc_fiveenterbase
                         where lrdw like '52%'
                           and lrsj >= to_date('2011-03-01', 'yyyy-MM-dd')
                           and lrsj <= to_date('2011-08-04', 'yyyy-MM-dd')) a,
                       ud_unit b
                 where a.lrdw = b.dwdm(+)
                 group by b.dwjc, dwdm, lb



           union all



           select b.dwjc,
                       nvl(0, 0) jcz,
                       nvl(0, 0) jsq,
                       nvl(0, 0) jdw,
                       nvl(0, 0) jxx,
                       nvl((CASE
                             WHEN jb = 1 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where jb = a.jb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as gj,
                       nvl((CASE
                             WHEN jb = 2 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where jb = a.jb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as sj,
                       nvl((CASE
                             WHEN jb = 3 then
                              (select count(id)
                                 from xc_fiveenterbase
                                where jb = a.jb
                                  and lrdw = b.dwdm)
                             else
                              0
                           end),
                           0) as dq
                  from (select *
                          From xc_fiveenterbase
                         where lrdw like '52%'
                           and lrsj >= to_date('2011-03-01', 'yyyy-MM-dd')
                           and lrsj <= to_date('2011-08-04', 'yyyy-MM-dd')) a,
                       ud_unit b
                 where a.lrdw = b.dwdm
                 group by b.dwjc, dwdm, jb

                 ) a group by a.dwjc order by sj