1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- Oracle -- WITH fruit_data AS ( SELECT 1 no , '01,03' fruit1, '01,02' fruit2, '02,04' fruit3 FROM dual UNION ALL SELECT 2, '01' , '' , '' FROM dual UNION ALL SELECT 3, '03,02' , '04' , '' FROM dual UNION ALL SELECT 4, '01,02,03' , '' , '02' FROM dual ) , fruit_code AS ( SELECT '01' cd, '사과' nm FROM dual UNION ALL SELECT '02' , '귤' FROM dual UNION ALL SELECT '03' , '수박' FROM dual UNION ALL SELECT '04' , '포도' FROM dual ) SELECT NVL(a.nm, '합계' ) nm , COUNT (b. no ) cnt , ROUND( RATIO_TO_REPORT( COUNT (b. no )) OVER(PARTITION BY GROUPING (a.cd)) * 100, 1) rat FROM fruit_code a LEFT OUTER JOIN fruit_data UNPIVOT (fruit FOR gb IN (fruit1, fruit2, fruit3)) b ON INSTR(b.fruit, a.cd) > 0 GROUP BY ROLLUP ((a.cd, a.nm)) ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | -- MariaDB -- WITH fruit_data AS ( SELECT 1 no , '01,03' fruit1, '01,02' fruit2, '02,04' fruit3 UNION ALL SELECT 2, '01' , null , null UNION ALL SELECT 3, '03,02' , '04' , null UNION ALL SELECT 4, '01,02,03' , null , '02' ) , fruit_code AS ( SELECT '01' cd, '사과' nm UNION ALL SELECT '02' , '귤' UNION ALL SELECT '03' , '수박' UNION ALL SELECT '04' , '포도' ) SELECT IFNULL(SUBSTR(nm, 3), '합계' ) nm , cnt , ROUND(cnt / MAX (cnt) OVER() * 100, 1) rat FROM ( SELECT CONCAT(cd, nm) nm , SUM ( IFNULL(SIGN(INSTR(fruit1, cd)), 0) + IFNULL(SIGN(INSTR(fruit2, cd)), 0) + IFNULL(SIGN(INSTR(fruit3, cd)), 0) ) cnt FROM fruit_code a LEFT OUTER JOIN fruit_data b ON 1=1 GROUP BY CONCAT(cd, nm) WITH ROLLUP ) a ; |
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT ifnull(code, '합계' ) code, sum (cnt) cnt, sum (rate) rate FROM ( SELECT code, count (code) AS cnt, round( count (code) / sum ( count (*)) OVER() * 100, 1) AS rate FROM ( SELECT NO , SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',' , no ), ',' , -1) code, str FROM ( SELECT concat(ifnull(fruit1, '' ), ',' , IFNULL(fruit2, '' ), ',' , ifnull(fruit3, '' )) str FROM tmp) a, ( SELECT @ no := @ no + 1 AS NO FROM ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1, ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2, ( SELECT @ no := 0) t3) b WHERE no <= LENGTH(str) - LENGTH( REPLACE (str, ',' , '' )) + 1) aa WHERE code != '' GROUP BY code) bb GROUP BY code WITH ROLLUP ; |
mysql은 어렵네요. ;;