|
||
0
0
»ç¹øÀ» Å°·Î Àâ°í Àç±ÍÁ¶ÀÎÀ̶û 1¹ø °á°ú·Î ¼ºêÄõ¸®·Î ¾²½É µÉµí¿ä
2017-01-26
|
0
0
¡Û¶óÀÎ
Äõ¸® ½Ç·ÂÀÌ ¹Ì¾àÇؼ ½Ã°£ÀÌ ¿À·¡°É¸°µí...
Äõ¸® À߸øÂ¥¼ ..Èûµë.. WITH test AS ( SELECT 'È«±æµ¿' À̸§ , 12345 »ç¹ø , 'AÆÀ' ºÎ¼¸í , 2010 ±Ù¹«³âµµ , 100 ¸ÅÃâ¾× FROM dual UNION ALL SELECT 'È«±æµ¿' , 12345 , 'AÆÀ' , 2011 , 100 FROM dual UNION ALL SELECT 'È«±æµ¿' , 12345 , 'BÆÀ' , 2012 , 300 FROM dual UNION ALL SELECT 'È«±æ¼ø' , 23456 , 'AÆÀ' , 2010 , 400 FROM dual UNION ALL SELECT 'È«±æ¼ø' , 23456 , 'BÆÀ' , 2011 , 200 FROM dual UNION ALL SELECT 'È«±æ¼ø' , 23456 , 'BÆÀ' , 2012 , 100 FROM dual ) ,TB_MASTER AS ( SELECT »ç¹ø ,À̸§ ,ºÎ¼¸í ,SUM(¸ÅÃâ¾×) AS TOTAL -- ,SUM(¸ÅÃâ¾×) AS TOTAL FROM TEST GROUP BY (»ç¹ø, À̸§, ºÎ¼¸í) ORDER BY »ç¹ø ) SELECT »ç¹ø ,À̸§ ,MAX(ºÎ¼¸í) KEEP(DENSE_RANK LAST ORDER BY TOTAL) AS ºÎ¼ ,SUM(TOTAL) AS ÃѸÅÃâ FROM TB_MASTER GROUP BY (»ç¹ø, À̸§) |
0
0
¡Û¶óÀÎ
»ç¹ø À̸§ ºÎ¼ ÃѸÅÃâ
---------- ------ --- ---------- 12345 È«±æµ¿ BÆÀ 500 23456 È«±æ¼ø AÆÀ 700 2 rows selected. |
0
0
[* ºñȸ¿ø *]
select A.id, A.name, A.team, sum(A.sales_sum) as sales from (
SELECT *, sum(sales) as sales_sum FROM test group by name, team order by sales_sum desc )A group by A.name ù¹ø° ±×·ì¹ÙÀÌ·Î ¸ÅÃâ ÇÕ°è ³»°í Á¤·Ä·Î °³Àκ° ÆÀ ¼øÀ§ ¸Å±è µÎ¹ø° ±×·ì¹ÙÀÌ·Î ¸ÅÃâ Çհ踦 ÇÕ°èÇÔ. °³Àκ° ÆÀ ¼øÀ§´Â À§¿¡¼ Á¤·ÄµÇ¾î ù¹ø°°Í(¸ÅÃâÀÌ ³ôÀº ÆÀ)ÀÌ ³ëÃâÀ̵Ê. |
0
0
¾ÐµµÀû¸íÇ°
µÚ´Ê°Ô ±ÛÀ» È®ÀÎÇ߳׿ä.
¸ðµÎ ÀÚ¼¼ÇÑ ¼³¸í °¨»çÇÕ´Ï´Ù |
|
|
¡â ÀÌÀü±Û¡ä ´ÙÀ½±Û | ¸ñ·Ïº¸±â |