# E29

MR40219.java

原始 sql

SELECT 
    MAUR_BNOB_D.*, TNOB_SDATE, 
    BNOB_PITEM, BNOH_CHDATE, BNOB_CHDATE, 
    BASC_DEPT.DEPT_NAME, 
    DEPT2.DEPT_NAME AS DEPT2_MNAME, MNO_MNAME, 
    case nvl(BNOB_D_USEQTY,0) 
        WHEN 0 THEN 0 
        ELSE to_number(to_char(TO_DATE(TNOB_SDATE+19110000,'yyyymmdd')+CEIL((BNOB_D_STQTY+BNOB_D_QTY)/BNOB_D_USEQTY),'yyyymmdd'))-19110000 
    END AS BNOB_D_NEXTDATE 
FROM MAUR_BNOB_D, MAUR_BNOB, MAUR_BNOH, MAUR_TNOB, BASC_DEPT_ALL BASC_DEPT, BASC_DEPT DEPT2, BASC_MNO 
    WHERE 
		-- MAUR_BNOB_D x MAUR_BNOB
	    BNOB_D_ANO=BNOB_ANO AND BNOB_D_CHKIND=BNOB_CHKIND AND BNOB_D_CHYY=BNOB_CHYY AND BNOB_D_CHSEQ=BNOB_CHSEQ AND BNOB_D_SEQ=BNOB_SEQ AND 
	    -- MAUR_BNOH x MAUR_BNOB_D
	    BNOH_ANO=BNOB_D_ANO AND BNOH_CHKIND=BNOB_D_CHKIND AND BNOH_CHYY=BNOB_D_CHYY AND BNOH_CHSEQ=BNOB_D_CHSEQ AND 
	    -- MAUR_BNOH, MAUR_BNOB  x MAUR_TNOB
	    TNOB_ANO=BNOH_ANO AND TNOB_MKKIND=BNOH_MKKIND AND TNOB_MKYY=BNOH_MKYY AND TNOB_MKSEQ=BNOH_MKSEQ AND TNOB_SEQ=BNOB_SEQ AND 
	    -- MAUR_BNOB_D x BASC_DEPT
	    -- MAUR_BNOB_D x BASC_DEPT 2
	    -- MAUR_BNOB_D x BASC_MNO
	    BNOB_D_ANO3=BASC_DEPT.DEPT_ANO AND SUBSTR(BNOB_D_ANO3, 0, 4)=DEPT2.DEPT_ANO AND BNOB_MNO=MNO_MNO AND 
        (to_date(TO_CHAR(TNOB_SDATE+19110000),'yyyymmdd')>=to_date(20220101,'yyyymmdd')) AND 
        (to_date(TO_CHAR(TNOB_SDATE+19110000),'yyyymmdd')<=to_date(20220810,'yyyymmdd')) AND 
		-- 起迄日
		-- 判斷區處、場站範圍
		 (BNOB_D_ANO LIKE '06%') AND 
		 -- 限制只能查詢藥品類
		 BNOB_MNO LIKE 'N0%' 
		 ORDER BY DEPT2_MNAME, MNO_MNAME, BNOB_D_ANO3 ,BNOB_PITEM, TNOB_SDATE
         

應改作類似：

SELECT 
    A.* ,
    B.BNOB_PITEM, C.BNOH_CHDATE, B.BNOB_CHDATE, 
    E1.DEPT_NAME, E2.DEPT_NAME AS DEPT2_MNAME, 
    F.MNO_MNAME, 
    CASE NVL(BNOB_D_USEQTY,0) 
        WHEN 0 THEN 0 
        ELSE TO_NUMBER(TO_CHAR(TO_DATE(TNOB_SDATE+19110000,'YYYYMMDD')+CEIL((BNOB_D_STQTY+BNOB_D_QTY)/BNOB_D_USEQTY),'YYYYMMDD'))-19110000 
    END AS BNOB_D_NEXTDATE 
FROM MAUR_BNOB_D A
LEFT JOIN MAUR_BNOB B ON A.BNOB_D_ANO=B.BNOB_ANO AND A.BNOB_D_CHKIND=BNOB_CHKIND AND A.BNOB_D_CHYY=B.BNOB_CHYY AND A.BNOB_D_CHSEQ=B.BNOB_CHSEQ AND A.BNOB_D_SEQ=B.BNOB_SEQ 
LEFT JOIN MAUR_BNOH C ON C.BNOH_ANO=A.BNOB_D_ANO AND C.BNOH_CHKIND=A.BNOB_D_CHKIND AND C.BNOH_CHYY=A.BNOB_D_CHYY AND C.BNOH_CHSEQ=A.BNOB_D_CHSEQ 
LEFT JOIN MAUR_TNOB D ON D.TNOB_ANO=C.BNOH_ANO AND D.TNOB_MKKIND=C.BNOH_MKKIND AND D.TNOB_MKYY=C.BNOH_MKYY AND D.TNOB_MKSEQ=C.BNOH_MKSEQ AND D.TNOB_SEQ=B.BNOB_SEQ 
LEFT JOIN BASC_DEPT_ALL E1 ON A.BNOB_D_ANO3=E1.DEPT_ANO 
LEFT JOIN BASC_DEPT E2 ON SUBSTR(A.BNOB_D_ANO3, 0, 4)=E2.DEPT_ANO
LEFT JOIN BASC_MNO F ON B.BNOB_MNO=F.MNO_MNO 
where 1=1
    AND A.BNOB_D_ANO LIKE '06%' 
    AND B.BNOB_MNO LIKE 'N0%' 
ORDER BY E2.DEPT_NAME, F.MNO_MNAME, A.BNOB_D_ANO3 ,B.BNOB_PITEM, D.TNOB_SDATE

應該用 partition by, max() 先取得各分類數目決定頁數後再印製
這樣程式碼比較不會太複雜，類似這樣：

SELECT A.DEPT_NAME, A.DEPT2_MNAME, MAX(A.SEQ) 資料數, CEIL(MAX(A.SEQ)/24) 頁數 FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY E1.DEPT_ANO, E2.DEPT_ANO ORDER BY  C.BNOH_CHDATE) AS SEQ,
        A.* ,
        B.BNOB_PITEM, C.BNOH_CHDATE, B.BNOB_CHDATE, 
        E1.DEPT_NAME, E2.DEPT_NAME AS DEPT2_MNAME, 
        F.MNO_MNAME, 
        CASE NVL(BNOB_D_USEQTY,0) 
            WHEN 0 THEN 0 
            ELSE TO_NUMBER(TO_CHAR(TO_DATE(TNOB_SDATE+19110000,'YYYYMMDD')+CEIL((BNOB_D_STQTY+BNOB_D_QTY)/BNOB_D_USEQTY),'YYYYMMDD'))-19110000 
        END AS BNOB_D_NEXTDATE 
    FROM MAUR_BNOB_D A
    LEFT JOIN MAUR_BNOB B ON A.BNOB_D_ANO=B.BNOB_ANO AND A.BNOB_D_CHKIND=BNOB_CHKIND AND A.BNOB_D_CHYY=B.BNOB_CHYY AND A.BNOB_D_CHSEQ=B.BNOB_CHSEQ AND A.BNOB_D_SEQ=B.BNOB_SEQ 
    LEFT JOIN MAUR_BNOH C ON C.BNOH_ANO=A.BNOB_D_ANO AND C.BNOH_CHKIND=A.BNOB_D_CHKIND AND C.BNOH_CHYY=A.BNOB_D_CHYY AND C.BNOH_CHSEQ=A.BNOB_D_CHSEQ 
    LEFT JOIN MAUR_TNOB D ON D.TNOB_ANO=C.BNOH_ANO AND D.TNOB_MKKIND=C.BNOH_MKKIND AND D.TNOB_MKYY=C.BNOH_MKYY AND D.TNOB_MKSEQ=C.BNOH_MKSEQ AND D.TNOB_SEQ=B.BNOB_SEQ 
    LEFT JOIN BASC_DEPT_ALL E1 ON A.BNOB_D_ANO3=E1.DEPT_ANO 
    LEFT JOIN BASC_DEPT E2 ON SUBSTR(A.BNOB_D_ANO3, 0, 4)=E2.DEPT_ANO
    LEFT JOIN BASC_MNO F ON B.BNOB_MNO=F.MNO_MNO 
    WHERE 1=1
        AND A.BNOB_D_ANO LIKE '06%' 
        AND B.BNOB_MNO LIKE 'N0%' 
    ORDER BY E2.DEPT_NAME, F.MNO_MNAME, A.BNOB_D_ANO3 ,B.BNOB_PITEM, D.TNOB_SDATE
) A
GROUP BY A.DEPT_NAME, A.DEPT2_MNAME