'엽기sql'에 해당되는 글 1건

SELECT BB.seoul_CD,
       COUNT( DECODE(AA.S_AVG, '1', 1, NULL) ) G_1,
       COUNT( DECODE(AA.S_AVG, '2', 1, NULL) ) G_2,
       COUNT( DECODE(AA.S_AVG, '3', 1, NULL) ) G_3,
       COUNT( DECODE(AA.S_AVG, '4', 1, NULL) ) G_4,
       COUNT( DECODE(AA.S_AVG, '5', 1, NULL) ) G_5,
       COUNT( DECODE(AA.S_AVG, '6', 1, NULL) ) G_6,
       COUNT( DECODE(AA.S_AVG, '7', 1, NULL) ) G_7,
       COUNT( DECODE(AA.S_AVG, '8', 1, NULL) ) G_8,
       COUNT( DECODE(AA.S_AVG, '9', 1, NULL) ) G_9
  FROM (SELECT *
          FROM seoul 
          where seoul_year = '2007'
   and seoul_div = '20'
   and seoul_da = '21'
   and seoul_qe is not null) BB,
(
select seoul_da
       ROUND( (decode(sign(merong1 - 4), -1, 1, 0, 1,
            decode(sign(merong1 - 11), -1, 2, 0, 2,
             decode(sign(merong1 - 23), -1, 3, 0, 3,
              decode(sign(merong1 - 40), -1, 4, 0, 4,
               decode(sign(merong1 - 60), -1, 5, 0, 5,
                decode(sign(merong1 - 77), -1, 6, 0, 6,
                 decode(sign(merong1 - 89), -1, 7, 0, 7,
                  decode(sign(merong1 - 96), -1, 8, 0, 8,
                   decode(sign(merong1 - 100), -1, 9, 0, 9,0)))))))))+
            decode(sign(merong2 - 4), -1, 1, 0, 1,
             decode(sign(merong2 - 11), -1, 2, 0, 2,
              decode(sign(merong2 - 23), -1, 3, 0, 3,
               decode(sign(merong2 - 40), -1, 4, 0, 4,
                decode(sign(merong2 - 60), -1, 5, 0, 5,
                 decode(sign(merong2 - 77), -1, 6, 0, 6,
                  decode(sign(merong2 - 89), -1, 7, 0, 7,
                   decode(sign(merong2 - 96), -1, 8, 0, 8,
                    decode(sign(merong2 - 100), -1, 9, 0, 9,0)))))))))+
             decode(sign(merong3 - 4), -1, 1, 0, 1,
            decode(sign(merong3 - 11), -1, 2, 0, 2,
             decode(sign(merong3 - 23), -1, 3, 0, 3,
              decode(sign(merong3 - 40), -1, 4, 0, 4,
               decode(sign(merong3 - 60), -1, 5, 0, 5,
                decode(sign(merong3 - 77), -1, 6, 0, 6,
                 decode(sign(merong3 - 89), -1, 7, 0, 7,
                  decode(sign(merong3 - 96), -1, 8, 0, 8,
                   decode(sign(merong3 - 100), -1, 9, 0, 9,0)))))))))+
            decode(sign(merong4 - 4), -1, 1, 0, 1,
             decode(sign(merong4 - 11), -1, 2, 0, 2,
              decode(sign(merong4 - 23), -1, 3, 0, 3,
               decode(sign(merong4 - 40), -1, 4, 0, 4,
                decode(sign(merong4 - 60), -1, 5, 0, 5,
                 decode(sign(merong4 - 77), -1, 6, 0, 6,
                  decode(sign(merong4 - 89), -1, 7, 0, 7,
                   decode(sign(merong4 - 96), -1, 8, 0, 8,
                    decode(sign(merong4 - 100), -1, 9, 0, 9,0)))))))))+
            decode(sign(merong5 - 4), -1, 1, 0, 1,
            decode(sign(merong5 - 11), -1, 2, 0, 2,
             decode(sign(merong5 - 23), -1, 3, 0, 3,
              decode(sign(merong5 - 40), -1, 4, 0, 4,
               decode(sign(merong5 - 60), -1, 5, 0, 5,
                decode(sign(merong5 - 77), -1, 6, 0, 6,
                 decode(sign(merong5 - 89), -1, 7, 0, 7,
                  decode(sign(merong5 - 96), -1, 8, 0, 8,
                   decode(sign(merong5 - 100), -1, 9, 0, 9,0)))))))))+
            decode(sign(puhahaha1 - 4), -1, 1, 0, 1,
             decode(sign(puhahaha1 - 11), -1, 2, 0, 2,
              decode(sign(puhahaha1 - 23), -1, 3, 0, 3,
               decode(sign(puhahaha1 - 40), -1, 4, 0, 4,
                decode(sign(puhahaha1 - 60), -1, 5, 0, 5,
                 decode(sign(puhahaha1 - 77), -1, 6, 0, 6,
                  decode(sign(puhahaha1 - 89), -1, 7, 0, 7,
                   decode(sign(puhahaha1 - 96), -1, 8, 0, 8,
                    decode(sign(puhahaha1 - 100), -1, 9, 0, 9,0)))))))))+
            decode(sign(puhahaha2 - 4), -1, 1, 0, 1,
            decode(sign(puhahaha2 - 11), -1, 2, 0, 2,
             decode(sign(puhahaha2 - 23), -1, 3, 0, 3,
              decode(sign(puhahaha2 - 40), -1, 4, 0, 4,
               decode(sign(puhahaha2 - 60), -1, 5, 0, 5,
                decode(sign(puhahaha2 - 77), -1, 6, 0, 6,
                 decode(sign(puhahaha2 - 89), -1, 7, 0, 7,
                  decode(sign(puhahaha2 - 96), -1, 8, 0, 8,
                   decode(sign(puhahaha2 - 100), -1, 9, 0, 9,0)))))))))+
            decode(sign(puhahaha3 - 4), -1, 1, 0, 1,
             decode(sign(puhahaha3 - 11), -1, 2, 0, 2,
              decode(sign(puhahaha3 - 23), -1, 3, 0, 3,
               decode(sign(puhahaha3 - 40), -1, 4, 0, 4,
                decode(sign(puhahaha3 - 60), -1, 5, 0, 5,
                 decode(sign(puhahaha3 - 77), -1, 6, 0, 6,
                  decode(sign(puhahaha3 - 89), -1, 7, 0, 7,
                   decode(sign(puhahaha3 - 96), -1, 8, 0, 8,
                    decode(sign(puhahaha3 - 100), -1, 9, 0, 9,0)))))))))+
          decode(sign(puhahaha4 - 4), -1, 1, 0, 1,
             decode(sign(puhahaha4 - 11), -1, 2, 0, 2,
              decode(sign(puhahaha4 - 23), -1, 3, 0, 3,
               decode(sign(puhahaha4 - 40), -1, 4, 0, 4,
                decode(sign(puhahaha4 - 60), -1, 5, 0, 5,
                 decode(sign(puhahaha4 - 77), -1, 6, 0, 6,
                  decode(sign(puhahaha4 - 89), -1, 7, 0, 7,
                   decode(sign(puhahaha4 - 96), -1, 8, 0, 8,
                    decode(sign(puhahaha4 - 100), -1, 9, 0, 9, 0))))))))) )/9  ) S_AVG
  from seoul
 where seoul_year = '2007'
   and seoul_div = '20'
   and seoul0_da = '21'
   and seoul_qe is not null
) AA
WHERE AA.seoul_qe = BB.seoul_qe
GROUP BY BB.seoul_cd;