p198.sql

来自「oracal的入门知识」· SQL 代码 · 共 34 行

INSERT INTO E$KSMSP
SELECT      a.ksmchcom,
         SUM (a.CHUNK) CHUNK,
         SUM (a.recr) recr,
         SUM (a.freeabl) freeabl,
         SUM (a.SUM) SUM
    FROM (SELECT   ksmchcom, COUNT (ksmchcom) CHUNK,
                   DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
                   DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
                   SUM (ksmchsiz) SUM
              FROM x$ksmsp
          GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom
/


select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk - b.chunk) c_diff,(a.sum -b.sum) s_diff
from
(SELECT   a.ksmchcom,
         SUM (a.CHUNK) CHUNK,
         SUM (a.recr) recr,
         SUM (a.freeabl) freeabl,
         SUM (a.SUM) SUM
    FROM (SELECT   ksmchcom, COUNT (ksmchcom) CHUNK,
                   DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,
                   DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,
                   SUM (ksmchsiz) SUM
              FROM x$ksmsp
          GROUP BY ksmchcom, ksmchcls) a
GROUP BY a.ksmchcom) a,e$ksmsp b
where a.ksmchcom = b.ksmchcom and (a.chunk - b.chunk) <>0
/