MySQL 의 WITH ROLLUP 은 괄호 사용이 안되고
GROUPING 이나 GROUPING_ID 기능도 없어 불편하네요.
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 31 32 33 34 35 36 37 38 39 40 41 42 | -- MariaDB -- WITH hospital AS ( SELECT 1 hospitalNo, '병원A' hospitalName UNION ALL SELECT 2, '병원B' UNION ALL SELECT 3, '병원C' ) , user AS ( SELECT 1 userNo, '길동' userName, 1 hospitalNo, 1 costNo UNION ALL SELECT 2, '영수' , 1, 2 UNION ALL SELECT 3, '철수' , 2, 3 UNION ALL SELECT 4, '민수' , 3, 4 ) , cost AS ( SELECT 1 costNo, 1 cnt, 10000 cost UNION ALL SELECT 1, 2, 5000 UNION ALL SELECT 2, 1, 30000 UNION ALL SELECT 3, 1, 7000 UNION ALL SELECT 4, 1, 2000 ) SELECT a.hospitalNo , a.hospitalName , IFNULL(b.userName, '소계' ) userName , SUM (c.cost) cost , SUM (c.cnt) cnt , CONCAT( CASE WHEN a.hospitalNo IS NULL THEN 1 ELSE 0 END , CASE WHEN a.hospitalName IS NULL THEN 1 ELSE 0 END , CASE WHEN b.userNo IS NULL THEN 1 ELSE 0 END , CASE WHEN b.userName IS NULL THEN 1 ELSE 0 END ) gid FROM hospital a INNER JOIN user b ON a.hospitalNo = b.hospitalNo INNER JOIN cost c ON b.costNo = c.costNo GROUP BY a.hospitalNo, a.hospitalName , b.userNo, b.userName WITH ROLLUP HAVING gid IN ( '0000' , '0011' ) ; |