데이터베이스 질문과 답변
예제 쿼리가 아래와 같을때
맨밑에 총계만 맨위로 올라가고 나머지는 정렬을 그대로 유지하고 싶습니다.
order 구문을 어떻게 써야 하는지요 ?
SELECT decode(grouping(R.com), 1, '총계', R.com) com
, decode(grouping(R.com) + grouping(R.subcom), 1, '소계', R.subcom) subcom
, SUM(CASE WHEN R.sub='kor' THEN R.val END) kor
, SUM(CASE WHEN R.sub='math' THEN R.val END) math
, SUM(CASE WHEN R.sub='eng' THEN R.val END) eng
FROM
(
SELECT 'A' nm, 'C1' com, 'C1-1'subcom, 'kor' sub, 10 val FROM dual
UNION ALL SELECT 'A' nm, 'C1', 'C1-1', 'math' sub, 20 val FROM dual
UNION ALL SELECT 'A' nm, 'C1', 'C1-1', 'eng' sub, 30 val FROM dual
UNION ALL SELECT 'B' nm, 'C2', 'C2-1', 'kor' sub, 200 val FROM dual
UNION ALL SELECT 'B' nm, 'C2', 'C2-1', 'math' sub, 300 val FROM dual
) R
GROUP BY ROLLUP(R.com, R.subcom)
맨밑에 총계만 맨위로 올라가고 나머지는 정렬을 그대로 유지하고 싶습니다.
order 구문을 어떻게 써야 하는지요 ?
SELECT decode(grouping(R.com), 1, '총계', R.com) com
, decode(grouping(R.com) + grouping(R.subcom), 1, '소계', R.subcom) subcom
, SUM(CASE WHEN R.sub='kor' THEN R.val END) kor
, SUM(CASE WHEN R.sub='math' THEN R.val END) math
, SUM(CASE WHEN R.sub='eng' THEN R.val END) eng
FROM
(
SELECT 'A' nm, 'C1' com, 'C1-1'subcom, 'kor' sub, 10 val FROM dual
UNION ALL SELECT 'A' nm, 'C1', 'C1-1', 'math' sub, 20 val FROM dual
UNION ALL SELECT 'A' nm, 'C1', 'C1-1', 'eng' sub, 30 val FROM dual
UNION ALL SELECT 'B' nm, 'C2', 'C2-1', 'kor' sub, 200 val FROM dual
UNION ALL SELECT 'B' nm, 'C2', 'C2-1', 'math' sub, 300 val FROM dual
) R
GROUP BY ROLLUP(R.com, R.subcom)
by 까망소
[2013.03.12 11:46:11]
--무식한 방법인지 모르겠으나 이렇게 해봤습니다. ^^:
select com
,subcom
,kor
,math
,eng
from (
SELECT decode(grouping(R.com), 1, '총계', R.com) com
, case when decode(grouping(R.com), 1, '총계', R.com) = '총계' then '1' else '2' end as seq_
, decode(grouping(R.com) + grouping(R.subcom), 1, '소계', R.subcom) subcom
, SUM(CASE WHEN R.sub='kor' THEN R.val END) kor
, SUM(CASE WHEN R.sub='math' THEN R.val END) math
, SUM(CASE WHEN R.sub='eng' THEN R.val END) eng
FROM
(
SELECT 'A' nm, 'C1' com, 'C1-1'subcom, 'kor' sub, 10 val FROM dual
UNION ALL SELECT 'A' nm, 'C1', 'C1-1', 'math' sub, 20 val FROM dual
UNION ALL SELECT 'A' nm, 'C1', 'C1-1', 'eng' sub, 30 val FROM dual
UNION ALL SELECT 'B' nm, 'C2', 'C2-1', 'kor' sub, 200 val FROM dual
UNION ALL SELECT 'B' nm, 'C2', 'C2-1', 'math' sub, 300 val FROM dual
) R
GROUP BY ROLLUP(R.com, R.subcom)
)
order by seq_, com, subcom
by 손님
[2013.03.12 12:04:01]
ORDER BY decode(grouping(R.com), 1, 1, 9),COM
,decode(grouping(R.subcom), 1, 9, 1),SUBCOM
,decode(grouping(R.subcom), 1, 9, 1),SUBCOM
by 마농
[2013.03.12 14:33:39]
-- 여러가지 방법이 있겠지요.
ORDER BY GROUPING(R.com) DESC, R.com, R.subcom
ORDER BY R.com NULLS FIRST, R.subcom
-- 여러컬럼에 대한 Grouping 은 Grouping_id 로 사용하세요.
decode(grouping(R.com) + grouping(R.subcom), 1, '소계', R.subcom)
decode(grouping_ID(R.com, R.subcom), 1, '소계', R.subcom)
ORDER BY GROUPING(R.com) DESC, R.com, R.subcom
ORDER BY R.com NULLS FIRST, R.subcom
-- 여러컬럼에 대한 Grouping 은 Grouping_id 로 사용하세요.
decode(grouping(R.com) + grouping(R.subcom), 1, '소계', R.subcom)
decode(grouping_ID(R.com, R.subcom), 1, '소계', R.subcom)
) 버튼을 클릭하여 작성 하시면 됩니다.