0
 45   5   2
  View Articles

Name  
   ¼Õº´¸ñ  (2002-03-28 00:25:01, Hit : 43563, Vote : 1712)
Homepage  
   http://www.itmembers.net
Subject  
   ¿À¶óŬ µ¥ÀÌÅÍ °Ë»ö - ORDER BY, GROUP BY
À̹ø ½Ã°£¿¡´Â µ¥ÀÌÅÍ °Ë»ö¿¡ »ç¿ëµÇ´Â ORDER BY¿Í GROUP BY ¹®¿¡ ´ëÇØ ¾Ë¾Æº¸±â·Î ÇÏ°Ú½À´Ï´Ù.
ORDER BY¿Í GROUP BY´Â SELECT ¹®ÀÇ ¸»¹Ì¿¡ »ç¿ëµÇ¸ç °Ë»öµÈ °á°ú¸¦ ´Ù½Ã Á¤·ÄÇϰųª ±×·ìÀ¸·Î ¹­¾î¼­ º¸¿©ÁÖ´Â ±â´ÉÀ» ÇÕ´Ï´Ù.


  1. OREDER BY

    SELECT ¹®À¸·Î °Ë»öÇÑ °á°ú¸¦ ƯÁ¤ÇÑ Ä÷³À» ±âÁØÀ¸·Î Á¤·ÄÇÏ¿© º¸¿©ÁÖ°íÀÚ ÇÒ ¶§ »ç¿ëÇÕ´Ï´Ù.
    ´ÙÀ½°ú °°Àº Çü½ÄÀ¸·Î »ç¿ëÇÕ´Ï´Ù.

    SELECT Ä÷³1, Ä÷³2, ...
    INTO :º¯¼ö1, :º¯¼ö2, ...
    FROM Å×À̺í1, Å×À̺í2, ...
    WHERE Á¶°Ç
    ORDER BY column1 [asc], column2 desc, column3 ...

    ¿©±â¼­ ORDER BY µÚ¿¡ ³ª¿­ÇÑ Ä÷³ ¼ø¼­´ë·Î Á¤·ÄµË´Ï´Ù. Áï column1À» ±âÁØÀ¸·Î ¸ÕÀú Á¤·ÄÇÏ°í column1ÀÇ °ªÀÌ °°À» °æ¿ì column2¸¦ ±âÁØÀ¸·Î Á¤·ÄÇÑ´Ù´Â ¶æÀÔ´Ï´Ù.
    Ä÷³ À̸§ µÚÀÇ asc ¶Ç´Â desc´Â ¿À¸§Â÷¼ø, ³»¸²Â÷¼øÀ» ÀǹÌÇÕ´Ï´Ù. »ý·«ÇÏ¸é ±âº»ÀûÀ¸·Î ¿À¸§Â÷¼øÀ¸·Î Á¤·ÄµË´Ï´Ù.

    ÀÚ ´ÙÀ½°ú °°ÀÌ ÀÔ·ÂÇÏ°í ±× °á°ú¸¦ È®ÀÎÇØ º¸¼¼¿ä.

    SELECT LAST_NAME, FIRST_NAME
    FROM EMP
    ORDER BY LAST_NAME, FIRST_NAME;


    À§¿¡¼­ ORDER BY LAST_NAME, FIRST_NAME; ´ë½Å
    ORDER BY 1, 2;¿Í °°ÀÌ ½áµµ µË´Ï´Ù. SELECT ¹®¿¡¼­ »ç¿ëÇÑ Ä÷³ À̸§ ¼ø¼­´ë·Î 1, 2, 3...°ú °°ÀÌ ºÎ¿©Çؼ­ Ä÷³ À̸§ ´ë½Å ¼ýÀڷΠǥ½ÃÇصµ µÈ´Ù´Â ¶æÀÔ´Ï´Ù.

  2. GROUP BY

    SELECT ¹®À¸·Î °Ë»öÇÑ °á°ú¸¦ ƯÁ¤ Ä÷³À» ±âÁØÀ¸·Î ±×·ìÈ­ÇÏ¿© Ç¥½ÃÇÕ´Ï´Ù.
    ¿©±â¼­ ±×·ìÈ­¶ó´Â °ÍÀº ƯÁ¤ Ä÷³ÀÇ °ªÀÌ °°À» °æ¿ì ÇØ´ç ·¹Äڵ带 ¸ðµÎ ¹­¾î¼­ Çϳª·Î Ç¥½ÃÇÑ´Ù´Â ¶æÀÔ´Ï´Ù.

    ¸ÕÀú GROUP BY¸¦ »ç¿ëÇÒ ¶§ÀÇ ¹®Àå Çü½ÄÀº ´ÙÀ½°ú °°½À´Ï´Ù.

    SELECT Ä÷³1, Ä÷³2, Ä÷³ ÇÔ¼ö ...
            INTO :º¯¼ö1, :º¯¼ö2, ...
            FROM Å×À̺í1, Å×À̺í2, ...
            WHERE Á¶°Ç
            GROUP BY Ä÷³1, Ä÷³2, ...
            HAVING Á¶°Ç
            ORDER BY Ä÷³1, Ä÷³2,

    À§¿¡¼­ º¸´Â °Í°ú °°ÀÌ GROUP BY´Â ORDER BY À§¿¡¼­ »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.
    ±×¸®°í HAVING ÀýÀ» »ç¿ëÇÒ ¼ö Àִµ¥, µÚ¿¡¼­ ´Ù·ç°ÚÁö¸¸ HAVINGÀº GROUPÀ» ¸¸µå´Â Á¶°ÇÀ» ³ªÅ¸³À´Ï´Ù.

    ¹é¹®ÀÌ ºÒ¿©ÀÏ°ß. ÀÏ´Ü º¾½Ã´Ù.
    ¸ÕÀú ½Ç½ÀÀ» À§ÇØ ´ÙÀ½°ú DEPT_SALES¶ó´Â Å×À̺íÀ» Çϳª ¸¸µé¾î¾ß°Ú³×¿ä.



    À§¿¡¼­ º¸¸é DEPT_NO°¡ Áߺ¹µÇ´Â ·¹Äڵ尡 ÀÖÁÒ?
    ±×·¡¼­ Áö±Ý ½Ç½ÀÇÒ ³»¿ëÀº DEPT_NO°¡ Áߺ¹µÇ´Â °Í³¢¸® ¹­¾î¼­(±×·ìÈ­ÇÏ¿©) AMOUNTÀÇ Æò±ÕÀ» ±¸ÇÏ´Â °ÍÀÔ´Ï´Ù. Áï DEPT_NO°¡ 201ÀÎ °ÍÀÇ AMOUNT Æò±Õ, 280ÀÇ Æò±Õ, 501ÀÇ Æò±ÕÀ» ±¸ÇÏ´Â °ÍÀÌ ¸ñÀûÀÔ´Ï´Ù.

    ±×·¯±â À§Çؼ­ ¸ÕÀú Å×À̺íÀ» ¸¸µé¾î¾ß°ÚÁÒ.
    ¾Æ·¡ÀÇ Äڵ带 º¹»çÇÏ¿© ±×´ë·Î »ç¿ëÇÏ½Ã¸é µË´Ï´Ù.

    DROP TABLE DEPT_SALES;

    CREATE TABLE DEPT_SALES
    ( DEPT_NO        CHAR(3),
      MONTH                CHAR(2) NOT NULL,
      AMOUNT        NUMBER NOT NULL,
      PLACE                VARCHAR2(20),
      PERSONNEL        CHAR(2),
      CONSTRAINT        PK_DEPTNO_MONTH PRIMARY KEY(DEPT_NO, MONTH)
    );

    INSERT INTO DEPT_SALES VALUES ('201', '1', 450, 'SEOUL', '14');
    INSERT INTO DEPT_SALES VALUES ('201', '2', 500, 'PUSAN', '16');
    INSERT INTO DEPT_SALES VALUES ('280', '4', 300, 'SEOUL', '25');
    INSERT INTO DEPT_SALES VALUES ('501', '4', 100, 'SEOUL', '14');
    INSERT INTO DEPT_SALES VALUES ('501', '5', 150, 'SEOUL', '27');
    INSERT INTO DEPT_SALES VALUES ('501', '6', 150, 'KWANGJU', '16');


    ÀÌÁ¦ À§¿¡¼­ ¾ò°íÀÚ ÇÏ´Â °á°ú¸¦ ±¸Çϱâ À§ÇØ ´ÙÀ½°ú °°ÀÌ Äõ¸®¹®À» ÀÛ¼ºÇÕ´Ï´Ù.

    SELECT DEPT_NO, AVG(AMOUNT)
            FROM DEPT_SALES
            WHERE AMOUNT > 100
            GROUP BY DEPT_NO;


    À§ÀÇ ¹®ÀåÀ» ÀÌÇØÇϼ̴ٸé, ¹®Á¦ Çϳª ³»°Ú½À´Ï´Ù.
    Áö¿ª(PLACE)º°·Î ±×·ìÈ­ÇÏ¿© Áö¿ªº° AMOUNTÀÇ Æò±ÕÀ» ±¸Çغ¸¼¼¿ä. ´Ü, Áö¿ª¸íÀÇ ¿ª¼øÀ¸·Î Á¤·ÄÇϼ¼¿ä.
    ÇØ´äÀ» º¸½Ã·Á¸é ¾Æ·¡ ºó°÷À» ¸¶¿ì½º·Î µå·¡±×Çغ¸¼¼¿ä.


    SELECT PLACE, AVG(AMOUNT)
                      FROM DEPT_SALES
                      GROUP BY PLACE
                      ORDER BY PLACE DESC;



  3. HAVING Àý

    HAVING ÀýÀº ¹Ýµå½Ã GROUP Àý°ú ÇÔ²² »ç¿ëµË´Ï´Ù.
    SELECT ¹®ÀÇ ÀϹÝÀûÀÎ Á¶°ÇÀÌ WHERE Àý¿¡¼­ Ç¥½ÃÇÑ´Ù¸é, GROUP Àý¿¡ ´ëÇÑ Á¶°ÇÀº HAVING Àý¿¡ ¾¹´Ï´Ù.

    ¿¹¸¦ µé¾î,
    SELECT DEPT_NO, AVG(AMOUNT)
            FROM DEPT_SALES
            WHERE AMOUNT > 100
            GROUP BY DEPT_NO
            HAVING AVG(AMOUNT) > 200;


    À§ÀÇ ¹®ÀåÀº AMOUNT°¡ 100À» ÃÊ°úÇÏ´Â °Í Áß¿¡¼­ DEPT_NO¸¦ ±âÁØÀ¸·Î ±×·ìÈ­ÇϵÇ, ±×·ìÈ­ÇÑ AMOUNTÀÇ Æò±ÕÀÌ 200À» ÃÊ°úÇÏ´Â °Í¸¸ Ç¥½ÃÇ϶ó´Â ¶æÀÔ´Ï´Ù.

    À̹ø ½Ã°£¿¡´Â µü µÎ°¡Áö - ORDER BY¿Í GROUP BY¿¡ ´ëÇØ ¾Ë¾Æº¸¾Ò½À´Ï´Ù. ±×¸®°í GROUP BY´Â º°µµÀÇ Á¶°ÇÀýÀÎ HAVING ÀýÀ» »ç¿ëÇÒ ¼ö ÀÖ´Ù´Â °Íµµ ¾Ë¾Ò½À´Ï´Ù.
    ´ÙÀ½ ½Ã°£¿¡´Â ¿©·¯ Å×À̺íÀ» ´ë»óÀ¸·Î µ¥ÀÌÅ͸¦ °Ë»öÇϱâ À§ÇØ JOIN°ú UNION ¸í·É¿¡ ´ëÇØ ¾Ë¾Æ º¸°Ú½À´Ï´Ù.


¹ú½á ¸ñ¿äÀÏ »õº®ÀÔ´Ï´Ù.
´ë°³ ÇÑÁÖÀÇ ÇǷθ¦ °¡Àå ¸¹ÀÌ ´À³¢´Â ³¯ÀÌÁÒ. È­ÀÌÆÃ! Èû³À½Ã´Ù.

ÀÌ»ó µ¿Á־ƺü ¼Õº´¸ñÀ̾ú½À´Ï´Ù.

Prev
   ¿À¶óŬ µ¥ÀÌÅÍ °Ë»ö - JOIN (1) EquJoin

¼Õº´¸ñ
Next
   ¿À¶óŬ µ¥ÀÌÅÍ °Ë»ö - Scalar Function(3) º¯È¯Çü ÇÔ¼ö ¿Ü

¼Õº´¸ñ


Copyright 1999-2025 Zeroboard / skin by Zetyx