Oracle实战经验

使用Oracle过程中的一些经验

1.使用右连接时，可以查出右表所有数据。但是如果where条件中有左表的条件语句，就只能查出满足条件的行了（就不一定能查出所有的右表数据）。如下，右表m2全部数据应该有6条：“禅城区”、“南海区”、“顺德区”、“三水区”、“高明区”、“市直”。但是where条件中存在m1的条件（这里是m1.xz_id = ‘2’影响的），只能查出5条数据。

select nvl(sum(jjjyje), 0) jjjyje, m2.xzq_c_desc zjrs, m2.xzq_c_id xzq_z_id
  from (select month_id,
               substr(t.xzq_z_id, 0, 6) xzq_z_id,
               t4.xzq_c_desc zjrs,
               t.xz_id xz_id,
               sum(jjzcje) jjzcje,
               sum(jjjyje) jjjyje
          from T_JSC_JJZJQK t,
               D_JSC_XZ t3,
               (select distinct xzq_c_id, xzq_c_desc
                  from D_JSC_XZQ
                union
                select '440699', '市直'
                  from dual) t4
         where t.xz_id = t3.xz_id
           and t.xzq_z_id = t4.xzq_c_id
         group by t.xz_id,
                  t.month_id,
                  substr(t.xzq_z_id, 0, 6),
                  t4.xzq_c_desc,
                  t3.xz_desc
         order by substr(t.xzq_z_id, 0, 6), t.xz_id, t.month_id) m1,
       (select distinct xzq_c_id, xzq_c_desc
          from D_JSC_XZQ
        union
        select '440699', '市直'
          from dual) m2
 where m1.month_id = '202104'
   and m2.xzq_c_id like '4406%'
   and m1.xzq_z_id(+) = m2.xzq_c_id
   and m1.xz_id = '2'
 group by m2.xzq_c_desc, m2.xzq_c_id
 order by m2.xzq_c_id

--查询结果
  JJJYJE  ZJRS   XZQ_Z_ID
1	0	  禅城区	440604
2	0	  南海区	440605
3	0	  顺德区	440606
4	0	  三水区	440607
5	0	  高明区	440608

可以在整个sql外再套一层，如下可查出右表所有数据：

select nvl(jjjyje, 0) jjjyje, m2.xzq_c_desc zjrs, m2.xzq_c_id xzq_z_id
  from (select sum(jjjyje) jjjyje, zjrs, xzq_z_id
          from (select month_id,
                       substr(t.xzq_z_id, 0, 6) xzq_z_id,
                       t4.xzq_c_desc zjrs,
                       t.xz_id xz_id,
                       sum(jjzcje) jjzcje,
                       sum(jjjyje) jjjyje
                  from T_JSC_JJZJQK t,
                       D_JSC_XZ t3,
                       (select distinct xzq_c_id, xzq_c_desc
                          from D_JSC_XZQ
                        union
                        select '440699', '市直'
                          from dual) t4
                 where t.xz_id = t3.xz_id
                   and t.xzq_z_id = t4.xzq_c_id
                 group by t.xz_id,
                          t.month_id,
                          substr(t.xzq_z_id, 0, 6),
                          t4.xzq_c_desc,
                          t3.xz_desc
                 order by substr(t.xzq_z_id, 0, 6), t.xz_id, t.month_id)
         where month_id = '202104'
           and xzq_z_id like '4406%'
           and xz_id = '1'
         group by zjrs, xzq_z_id
         order by xzq_z_id) m1,
       (select distinct xzq_c_id, xzq_c_desc
          from D_JSC_XZQ
        union
        select '440699', '市直'
          from dual) m2
 where m1.xzq_z_id(+) = m2.xzq_c_id

--查询结果
  JJJYJE  ZJRS   XZQ_Z_ID
1	0	  禅城区	440604
2	0	  南海区	440605
3	0	  顺德区	440606
4	0	  三水区	440607
5	0	  高明区	440608
6	0	  市直	440699