SQL报表打印显示不同分组难题

Question

select t11.dept_no,t11.mzsum,t11.jzsum,t22.area_no,t22.intosum,t22.leasum from 
(select rownum id,t1.* from (select dept_no,sum(mz_qty) mzsum,sum(jz_qty) jzsum from mzgb group by dept_no) t1) t11,
(select rownum id,t2.* from (select area_no,sum(into_qty) intosum,sum(leave_qty) leasum from zyrb group by area_no) t2) t22
where t11.id=t22.id;

Answer 1

这是按bzszp(SongZip)方法的运行结果：DEPT      MZSUM      JZSUM AREA    INTOSUM     LEASUM
---- ---------- ---------- ---- ---------- ----------
01           15          8 02            3          5
03           43        135 06
05            4          4 10            1          5还没有达到目的，我增加了门诊06、07的数据没有显示，我要的结果是下图：DEPT      MZSUM      JZSUM AREA    INTOSUM     LEASUM
---- ---------- ---------- ---- ---------- ----------
01           15          8 02            3          5
03           43        135 06
05            4          4 10            1          5
06　　　　　　 6
07            7　　请老大在指点一下　　　　　　　　　　

Answer 2

select dept_no,mzsum,jzsum,area_no,intosum,leasum from 
(select dept_no,sum(mz_qty) mzsum,sum(jz_qty) jzsum from mzgb group by dept_no),
(select area_no,sum(into_qty) intosum,sum(leave_qty) leasum from zyrb group by area_no)

Answer 3

楼上的，你的结果是这样，不行DEPT      MZSUM      JZSUM AREA    INTOSUM     LEASUM
---- ---------- ---------- ---- ---------- ----------
01           15          8 02            3          5
03           43        135 02            3          5
05            4          4 02            3          5
06            3          2 02            3          5
07            2          2 02            3          5
08            2          2 02            3          5
01           15          8 06
03           43        135 06
05            4          4 06
06            3          2 06
07            2          2 06DEPT      MZSUM      JZSUM AREA    INTOSUM     LEASUM
---- ---------- ---------- ---- ---------- ----------
08            2          2 06
01           15          8 10            1          5
03           43        135 10            1          5
05            4          4 10            1          5
06            3          2 10            1          5
07            2          2 10            1          5
08            2          2 10            1          5

Answer 4

select dept_no,mzsum,jzsum,area_no,intosum,leasum from 
(select dept_no,sum(mz_qty) mzsum,sum(jz_qty) jzsum from mzgb group by dept_no) a,
(select area_no,sum(into_qty) intosum,sum(leave_qty) leasum from zyrb group by area_no) b where a.rownum=b.rownum(+)
union
select dept_no,mzsum,jzsum,area_no,intosum,leasum from 
(select dept_no,sum(mz_qty) mzsum,sum(jz_qty) jzsum from mzgb group by dept_no) a,
(select area_no,sum(into_qty) intosum,sum(leave_qty) leasum from zyrb group by area_no) b where a.rownum(+)=b.rownum

Answer 5

假如一個sql不行的話呢,用temporary,然後呢用動態的sql,進行一行一行地update,即可

Answer 6

select t11.dept_no,t11.mzsum,t11.jzsum,t22.area_no,t22.intosum,t22.leasum from 
(select rownum id,t1.* from (select dept_no,sum(mz_qty) mzsum,sum(jz_qty) jzsum from mzgb group by dept_no) t1) t11,
(select rownum id,t2.* from (select area_no,sum(into_qty) intosum,sum(leave_qty) leasum from zyrb group by area_no) t2) t22
where t11.id=t22.id(+);

Answer 7

bzszp(SongZip) ，高人，搞店，谢谢！