최대값과 최소값 제외한 평균,총합 구하기(GREATEST,LEAST)

WITH TE AS (
SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL
)
SELECT  *
FROM    TE;

 

WITH TE AS (
SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL
UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL
UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL
UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '5' H FROM DUAL
UNION ALL SELECT '9' A, '7' B, '4' C, '5' D, '3' E, '4' F, '8' G, '2' H FROM DUAL
UNION ALL SELECT '8' A, '9' B, '4' C, '5' D, '3' E, '2' F, '4' G, '5' H FROM DUAL
)
SELECT   ROUND((A+B+C+D+E+F+G+H - GREATEST(A,B,C,D,E,F,G,H) - LEAST(A,B,C,D,E,F,G,H))/6) AS AVG
        ,GREATEST(A,B,C,D,E,F,G,H) AS MAX
        ,(A,B,C,D,E,F,G,H) AS MIN
        ,(A+B+C+D+E+F+G+H-GREATEST(A,B,C,D,E,F,G,H) - LEAST(A,B,C,D,E,F,G,H)) AS TOTAL
        ,TE.*
FROM    TE;