生成报表的相关sql 博客分类： oracle sql报表

一张(l 和 h是同一张表,起了不同的别名)保存了名称,机构编码,上级编码的机构编码表,通过sql语句进行加工,得到一张报表的行数据(横向多排),别且根据数据条件形成不同层次.

select code1, name1, name2
  from ((select h.org||'00' code1,
                l.name || ' ' name1,
                h.name name2
           from  l
           join  h
             on (l.org = h.orgtype)
          where   l.m=1 AND l.o = decode(,'000000', '-1', )) union all
        (select ||'00', '总计', '总计'
           from dual
         union all
         select l.org||'00' code1,
                 l.name || ' ' name1,
                '合计'||getNbsp(rownum) name2
           from l
          where l.m=1 AND l.org <> '000000'
            and l.o = decode(,'000000', '-1', ))
            union all
            select l.org||'01' code1,
                l.name || '&nbsp;' name1,
                l.name||decode(,'000000','(责任单位)','(合作单位)') name2
           from l
          where  l.m=1 AND l.o = decode(,'000000', '-1',)
            and l.org<>'000000'
               union all

               select l.org||'02' code1,
                l.name||'(责任单位)' name1,
                '合计' name2
           from l
          where  l.mgrtype=1 AND l.org = decode(,'000000', '-1',)
            )
order by code1