|











|
|
|
 |
0 |
|
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 ¹®ÀÇ ¸»¹Ì¿¡ »ç¿ëµÇ¸ç °Ë»öµÈ °á°ú¸¦ ´Ù½Ã Á¤·ÄÇϰųª ±×·ìÀ¸·Î ¹¾î¼ º¸¿©ÁÖ´Â ±â´ÉÀ» ÇÕ´Ï´Ù.

- 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...°ú °°ÀÌ ºÎ¿©Çؼ Ä÷³ À̸§ ´ë½Å ¼ýÀڷΠǥ½ÃÇصµ µÈ´Ù´Â ¶æÀÔ´Ï´Ù.
- 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;
- 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 ¸í·É¿¡ ´ëÇØ ¾Ë¾Æ º¸°Ú½À´Ï´Ù.
¹ú½á ¸ñ¿äÀÏ »õº®ÀÔ´Ï´Ù.
´ë°³ ÇÑÁÖÀÇ ÇǷθ¦ °¡Àå ¸¹ÀÌ ´À³¢´Â ³¯ÀÌÁÒ. ÈÀÌÆÃ! Èû³À½Ã´Ù.
ÀÌ»ó µ¿Á־ƺü ¼Õº´¸ñÀ̾ú½À´Ï´Ù.
|
 |
 |
|
|
|