p198.sql

oracal的入门知识

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
/