¸¶½ºÅÍÆ÷À¯(Master4U)
Monday, 17 of January
 

·Î±×ÀÎ Category
±ÝÁÖÀÇ °Ô½Ã¹° SQL·Î ÇÑÅ¥¿¡ »Ì¾Æ³»±â!
°ü¸®ÀÚ  2010-12-23 03:26:51 Hit:5906
¸µÅ© #1: http://jjjboard.com/zb/zboard.php?id=mysql&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=10

±ÝÁÖÀÇ °Ô½Ã¹° SQL·Î ÇÑÅ¥¿¡ »Ì¾Æ³»±â!

(2004-11-04 21:44:47, Hit : 10264, Vote : 341)

1> ½ÃÀÛÇϱ⿡ ¾Õ½á À߸øµÈ »ý°¢ ¹Ù·ÎÀâ±â
1.1> ÇÑÁÖÀÇ ½ÃÀÛÀº ÀÏ¿äÀÏÀÌ´Ù.
-> ¾î¶²ºÐµéÀº ¿ù¿äÀÏÀÌ ÇÑÁÖÀÇ ½ÃÀÛÀΰÍÀ¸·Î ¾Ë°íÀÖÁö¸¸ ÇÑÁÖÀÇ ½ÃÀÛÀº ÀÏ¿äÀÏÀÔ´Ï´Ù.

2> ½ÇÀü¿¡ ¾Õ½á...
2.1> MySQL¿¡¼­ ³¯Â¥°ü·Ã ÇÔ¼ö

2.1.1> DATE_ADD(a, INTERVAL b DAY)
-> a³¯Â¥¿¡¼­ b³¯¸¸Å­ ´õÇÑ´Ù.

2.1.2> DATE_SUB(a, INTERVAL b DAY)
-> a³¯Â¥¿¡¼­ b³¯¸¸Å­ »«´Ù.

2.1.3> CURDATE()
-> ÇöÀç ³¯Â¥¸¦ Ãâ·ÂÇÑ´Ù. cf> NOW() ÇÔ¼ö´Â ÇöÀ糯¥ ¹× ÇöÀç ½Ã°£À» Ãâ·ÂÇÑ´Ù.

2.1.4> DAYOFWEEK(a)
-> a³¯Â¥°¡ À§Ä¡ÇÑ ¿äÀÏÀÇ ÁÖ(weekday) À妽º°ªÀ» Ãâ·ÂÇÑ´Ù. ex>ÀÏ=1, ¿ù=2, Åä=7

3> ½ÇÀü¿¬½À

3.1> À̹øÁÖ Ã¹ ³¯Â¥¸¸ ¾Ë¾Æ³»±â(³¯Â¥±âÁØ)
SELECT DATE_SUB(CURDATE(), INTERVAL(DAYOFWEEK(CURDATE()) - 1) DAY)

3.2> ÇöÀç½Ã°¢ ±âÁØÀ¸·Î ù ³¯Â¥ ¹× ½Ã°£ ¾Ë¾Æ³»±â(ÇöÀç½Ã°¢ ±âÁØ)
SELECT DATE_SUB(NOW(), INTERVAL(DAYOFWEEK(NOW()) - 1) DAY)

3.3> À̹øÁÖ ³¡ ³¯Â¥¸¸ ¾Ë¾Æ³»±â(³¯Â¥±âÁØ)
SELECT DATE_ADD(CURDATE(), INTERVAL(7 - DAYOFWEEK(CURDATE())) DAY)

3.4> ÇöÀç½Ã°¢ ±âÁØÀ¸·Î ³¡ ³¯Â¥ ¹× ½Ã°£ ¾Ë¾Æ³»±â(ÇöÀç½Ã°¢ ±âÁØ)
SELECT DATE_ADD(NOW(), INTERVAL(7 - DAYOFWEEK(NOW())) DAY)

Àß º¸½Ã¸é...
CURDATE() °¡ NOW() ·Î ¹Ù²î½Å°ÍÀ» ¾Ë¼ö ÀÖ½À´Ï´Ù.
Å×À̺íÀ» ±¸¼ºÇϽǶ§ ÇʵåŸÀÔ ¼±¾ð¿¡ À־ À̰ÍÀÌ Á» Áß¿äÇÑ ÀÌÀ¯´Â...

¿¹¸¦µé¸é ³¯Â¥°¡ DATETIME, Áï '2004-01-17 13:45:12' ·Î ±â·ÏµÇ¾î Àִµ¥...
½ÃÀÛ³¯Â¥ÇÏ°í ³¡ ³¯Â¥¸¸ ¾Ë¾Æ³»¼­ ºñ±³±¸¹®(between)À» ¶§¸°´Ù¸é...
ºÐ¸í ³¡ÀÏ¿¡ ÇØ´çÇÏ´Â ¸®½ºÆ®µéÀº Ãâ·ÂÇϰíÀÚ ÇÏ´Â ¸®½ºÆ®¿¡¼­ Á¦¿ÜµÉ°ÍÀÔ´Ï´Ù.
¿Ö³ÄÇϸé
³¡ ³¯Â¥ '2004-01-17' À» '2004-01-17 00:00:00'À¸·Î ÀνÄÇϱ⠶§¹®ÀÌÁÒ...
ºÐ¸í '2004-01-17 00:00:00'´Â °°Àº 1¿ù 17ÀÏ À̱ä ÇÏÁö¸¸...
'2004-01-17 13:45:12' º¸´Ù´Â ÀÛ½À´Ï´Ù.
ÀÌÇØÇϼ̴ÂÁö ¸ð¸£°Ú³×¿©...
ÇʵåÀÇ ³¯Â¥ Æ÷¸Ë¿¡ µû¶ó SQLÀ» ÀûÀýÈ÷ Á¶ÀýÇÒ Çʿ䰡 ÀÖ½À´Ï´Ù.

¾Æ¹«Æ°...
ƯÁ¤³¯Â¥¿¡ ´ëÇØ¼­ Ãâ·ÂÇÏ·Á¸é...
CURDATE() ºÎºÐ¿¡ ƯÁ¤³¯Â¥¸¦ ³Ö¾îÁÖ¸é µÇ°ÚÁÒ?
¿¹¸¦µé¾î 2004³â 1¿ù 15ÀÏÀÇ ±× ÁÖ ½ÃÀÛÀϰú ³¡ÀÏÀ» ¾Ë°í½Í´Ù¸é

½ÃÀÛÀÏ : SELECT DATE_SUB('2004-01-15', INTERVAL(DAYOFWEEK('2004-01-15') - 1) DAY)

³¡ÀÏ : SELECT DATE_ADD('2004-01-15', INTERVAL(7 - DAYOFWEEK('2004-01-15')) DAY)

ÀÌÇØÇϼ̴ÂÁö ¸ð¸£°Ú±º¿©...^^;
ÀÚ...
orderlist(ÁÖ¹®¸®½ºÆ®) ¶õ Å×ÀÌºí¿¡ orderday(ÁÖ¹®ÀÏ) ¶ó´Â Çʵ尡 ÀÖ°í
orderday´Â DATE ŸÀÔ, Áï '2004-02-10' ÇüÅ·Π±â·ÏµÉ ¶§ ÁÖ¹®ÀÏÀÌ ±ÝÁÖÀÎ ÁÖ¹®¸®½ºÆ®¸¸ ÃßÃâÇÏ°í ½Í´Ù¸é ¾Æ·¡¿Í °°ÀÌ ÇÏ¸é µÇ°ÚÁÒ?

SELECT * FROM orderlist WHERE orderday BETWEEN DATE_SUB(CURDATE(), INTERVAL(DAYOFWEEK(CURDATE()) - 1) DAY) AND DATE_ADD(CURDATE(), INTERVAL(7 - DAYOFWEEK(CURDATE())) DAY)

ÀÌ»óÀÔ´Ï´Ù. ^^;

P.S>
ºÐ¸í ÇʵåÀÇ DATE Æ÷¸Ë¿¡ µû¶ó SQLÀ» ÀûÀýÈ÷ Á¶ÀýÇÏ¼Å¾ß ÇÑ´Ù°í ¸»¾¸µå·È±¸¿ä...
À§ÀÇ SQLÀº ÇʵåŸÀÔÀÌ DATE À϶§ Àû¿ëµÉ ¼ö ÀÖ½À´Ï´Ù.
ÀÌÇØÇÏ¼Ì±æ ¹Ù¶ó¸ç...^^;

EX)
//-- 15ÀÏ ÀÌÀüÀÇ µ¥ÀÌŸ »Ì±â.
$SQL = "select * from table_name Where DATE_FORMAT(regdate ,'%Y-%m-%d' ) < DATE_SUB(CURDATE(), INTERVAL 15 DAY) ";

-- add --
`DATE_ADD(date,INTERVAL expr type)'
`DATE_SUB(date,INTERVAL expr type)'
`ADDDATE(date,INTERVAL expr type)'
`SUBDATE(date,INTERVAL expr type)'


`type' *value* *Expected* `expr' *format*
`SECOND' `SECONDS'
`MINUTE' `MINUTES'
`HOUR' `HOURS'
`DAY' `DAYS'
`MONTH' `MONTHS'
`YEAR' `YEARS'
`MINUTE_SECOND' `"MINUTES:SECONDS"'
`HOUR_MINUTE' `"HOURS:MINUTES"'
`DAY_HOUR' `"DAYS HOURS"'
`YEAR_MONTH' `"YEARS-MONTHS"'
`HOUR_SECOND' `"HOURS:MINUTES:SECONDS"'
`DAY_MINUTE' `"DAYS HOURS:MINUTES"'
`DAY_SECOND' `"DAYS HOURS:MINUTES:SECONDS"'
º»¹®Àμ⺻¹®¸ÞÀϹ߼Û
¡â MySQL ±âº»°³³ä [1]
¡ä Å×À̺í(Table) º¹»ç...µ¥ÀÌŸ Æ÷ÇÔ
Copyright 1999-2025 Zeroboard / skin by ChanBi