sql where ºÍon µÄÇø±ð
½ñÌìÔÚÂÛ̳ÄÚ¿´µ½Ò»Æª ÃæÊÔÌ⣬ÊǹØÓÚwhere ºÍon µÄÇø±ðµÄ£¬×Ô¼ºÒ²²»¶®£¬ÔÚÍøÉÏËѳöһƪÎÄÕ£¬¼ÓÉî×Ô¼ºµÄ¼ÇÒä
ת
Êý¾Ý¿âÔÚͨ¹ýÁ¬½ÓÁ½ÕÅ»ò¶àÕűíÀ´·µ»Ø¼Ç¼ʱ£¬¶¼»áÉú³ÉÒ»ÕÅÖмäµÄÁÙʱ±í£¬È»ºóÔÙ½«ÕâÕÅÁÙʱ±í·µ»Ø¸øÓû§¡£
ÔÚʹÓÃleft jionʱ£¬onºÍwhereÌõ¼þµÄÇø±ðÈçÏ£º
1¡¢ onÌõ¼þÊÇÔÚÉú³ÉÁÙʱ±íʱʹÓõÄÌõ¼þ£¬Ëü²»¹ÜonÖеÄÌõ¼þÊÇ·ñΪÕ棬¶¼»á·µ»Ø×ó±ß±íÖеļǼ¡£
2¡¢whereÌõ¼þÊÇÔÚÁÙʱ±íÉú³ÉºÃºó£¬ÔÙ¶ÔÁÙʱ±í½øÐйýÂ˵ÄÌõ¼þ¡£ÕâʱÒѾûÓÐleft joinµÄº¬Ò壨±ØÐë·µ»Ø×ó±ß±íµÄ¼Ç¼£©ÁË£¬Ìõ¼þ²»ÎªÕæµÄ¾ÍÈ«²¿¹ýÂ˵ô¡£
¼ÙÉèÓÐÁ½ÕÅ±í£º
±í1£ºtab2
id
size
1
10
2
20
3
30
±í2£ºtab2
size
name
10
AAA
20
BBB
20
CCC
Á½ÌõSQL:
1¡¢select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2¡¢select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
µÚÒ»ÌõSQLµÄ¹ý³Ì£º
1¡¢Öмä±í
onÌõ¼þ:
tab1.size = tab2.size
tab1.id
tab1.size
tab2.size
tab2.name
1
10
10
AAA
2
20
20
BBB
2
20
20
CCC
3
30
(null)
(null)
|
|
2¡¢ÔÙ¶ÔÖмä±í¹ýÂË
where Ìõ¼þ£º
tab2.name=’AAA’
tab1.id
tab1.size
tab2.size
tab2.name
1
10
10
AAA
µÚ¶þÌõSQLµÄ¹ý³Ì£º
1¡¢Öмä±í
onÌõ¼þ:
tab1.size = tab2.size and tab2.name=’AAA’
(Ìõ¼þ²»ÎªÕæÒ²»á·µ»Ø×ó±íÖеļǼ)
tab1.id
tab1.size
tab2.size
tab2.name
1
10
10
AAA
2
20
(null)
(null)
3
30
(null)
(null)
ÆäʵÒÔÉϽá¹ûµÄ¹Ø¼üÔÒò¾ÍÊÇleft join,right join,full joinµÄÌØÊâÐÔ£¬²»¹ÜonÉϵÄÌõ¼þÊÇ·ñΪÕ涼»á·µ»Øleft»òright±íÖеļǼ£¬fullÔò¾ßÓÐleftºÍrightµÄÌØÐԵIJ¢¼¯¡£ ¶øinner jionûÕâ¸öÌØÊâÐÔ£¬ÔòÌõ¼þ·ÅÔÚonÖкÍwhereÖУ¬·µ»ØµÄ½á¹û¼¯ÊÇÏàͬµÄ
Ïà¹ØÎĵµ£º
----start
·²ÊÇÖªµÀÊý¾Ý¿âµÄÈ˶¼ÖªµÀSQL£¬·²ÊǶÔSQLÓÐÒ»µãÁ˽âµÄÈ˶¼¾õµÃSQLºÜ¼òµ¥£¬·²ÊÇÓÐÕâÖָоõµÄÈ˶¼ÊÇSQLµÃ³õ¼¶Óû§£¬ÒòΪËûѧ»áÁËÔö²éɾ¸Ä¾ÍÒÔΪÕâ¾ÍÊÇSQLµÄÈ«²¿¡£Ä¿Ç°µÄ´ó²¿·ÖÓ¦ÓÃÈí¼þ¶¼ÊÇÒÔÊý¾Ý¿âΪÖÐÐÄ£¬Ëæ×ÅÈí¼þµÄÔËÐУ¬Êý¾ÝÁ¿»áÔ½À´Ô½´ó¡£ÈçºÎÓüò½à¡¢¸ßЧµÄSQLÓï¾ä²Ù×÷Êý¾ÝÏÔµÃÔ½À ......
HR·þÎñÆ÷£º ²Ù×÷ϵͳ->32λWindows2000 SP4 Êý¾Ý¿â->32λSQL Server2000
OA·þÎñÆ÷£º ²Ù×÷ϵͳ->64λWindows2003 SP2 Êý¾Ý¿â->64λSQL Server2005
OAÏî ......
----start
´ó¼Ò¶ÔIF ELSEÓï¾ä¿ÉÄܶ¼ºÜÊìϤ£¬ËüÊÇÓÃÀ´¶Ô¹ý³Ì½øÐпØÖƵġ£ÔÚSQLµÄÊÀ½çÖÐCASEÓï¾äÓï¾äÓÐÀàËƵÄЧ¹û¡£ÏÂÃæ¼òµ¥µÄ½éÉÜCASEÓï¾äµÄÓ÷¨¡£¿¼ÂÇÏÂÃæµÄÇé¿ö£¬¼ÙÉèÓиöuser±í£¬¶¨ÒåÈçÏ£º
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,---ÐÕÃû
SEX INTEGER,---ÐÔ±ð£¨1¡¢ÄÐ 2¡¢Å®£©
BIRTHDA ......
ÔÚÑ°ÕÒPowerDesignerÏà¹ØÐÅÏ¢µÄʱºòżȻ·¢ÏÖµÄ.ʹÓÃtimestamp
΢ÈíMSDNÄÚÈÝ:
timestamp (Transact-SQL)
¹«¿ªÊý¾Ý¿âÖÐ×Ô¶¯Éú³ÉµÄΨһ¶þ½øÖÆÊý×ÖµÄÊý¾ÝÀàÐÍ¡£timestamp ͨ³£ÓÃ×÷¸ø±íÐмӰ汾´ÁµÄ»úÖÆ¡£ ´æ´¢´óСΪ 8 ¸ö×Ö½Ú¡£ timestamp Êý¾ÝÀàÐÍÖ»ÊǵÝÔöµÄÊý×Ö£¬²»±£ÁôÈÕÆÚ»òʱ¼ä¡£ ÈôÒª¼Ç¼ÈÕÆÚ»òʱ¼ä£¬ÇëʹÓà datetime ......
## ɾ³ýÊý¾Ý¿â
drop database ecport;
## ´´½¨Êý¾Ý¿â
create database ecport;
## ʹÓÃÊý¾Ý¿â
use ecport;
## ¹ú¼Ò±í½á¹¹
create table COUNTRY(
&nbs ......