SQLÖÐonÌõ¼þÓëwhereÌõ¼þµÄÇø±ð (having)
SQLÖÐonÌõ¼þÓëwhereÌõ¼þµÄÇø±ð
Êý¾Ý¿âÔÚͨ¹ýÁ¬½ÓÁ½ÕÅ»ò¶àÕűíÀ´·µ»Ø¼Ç¼ʱ£¬¶¼»áÉú³ÉÒ»ÕÅÖмäµÄÁÙʱ±í£¬È»ºóÔÙ½«ÕâÕÅÁÙʱ±í·µ»Ø¸øÓû§¡£
ÔÚʹÓÃ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ÖУ¬·µ»ØµÄ½á¹û¼¯ÊÇÏàͬµÄ¡£
on¡¢where¡¢havingµÄÇø±ð
2007-03-14 16:02
on¡¢where¡¢havingÕâÈý¸ö¶¼¿ÉÒÔ¼ÓÌõ¼þµÄ×Ó¾äÖУ¬onÊÇ×îÏÈÖ´ÐУ¬where´ÎÖ®£¬having×îºó¡£ÓÐʱºòÈç¹ûÕâÏȺó˳Ðò²»Ó°ÏìÖмä½á¹ûµÄ»°£¬ÄÇ×îÖÕ½á¹ûÊÇÏàͬµÄ¡£µ«ÒòΪonÊÇÏȰѲ»·ûºÏÌõ¼þµÄ¼Ç¼¹ýÂ˺ó²Å½øÐÐͳ¼Æ£¬Ëü¾Í¿ÉÒÔ¼õÉÙÖмäÔËËãÒª´¦ÀíµÄÊý¾Ý£¬°´Àí˵Ӧ¸ÃËÙ¶ÈÊÇ×î¿ìµÄ¡£
Ïà¹ØÎĵµ£º
1.Ò»µÀSQLÓï¾äÃæÊÔÌ⣬¹ØÓÚgroup by
±íÄÚÈÝ£º
2005-05-09 ʤ
2005-05-09 ʤ
2005-05-09 ¸º
2005-05-09 ¸º
2005-05-10 ʤ
2005-05-10 ¸º
2005-05-10 ¸º
Èç¹ûÒªÉú³ÉÏÂÁнá¹û, ¸ÃÈçºÎдsqlÓï¾ä?
ʤ ¸º
2005-05-09 2 2
2005-05-10 ......
ÊÀʶ´Ã÷½ÔѧÎÊ£¬ÈËÇéÁ·´ï¼´ÎÄÕ¡£×öASPʱ£¬×î³£ÓõÄÊý¾Ý¿â¼´SqlserverÓëAccessÊý¾Ý¿âĪÊôÁË£¡
µ«Ê¹Óûᾳ£·¢ÏֺܶàSQLÖ´ÐеÄÎÊÌâ¡£ÕâÀïÕûÀí³öÖ®¼äµÄ²îÒ죬×ö¸öÊ®´ó²îÒìµÄ×ܽᡣ
ACCESS½á¹¹¼òµ¥ÈÝÒ×´¦Àí£¬¶øÇÒÒ²ÄÜÂú×ã¶àÊýµÄÍøÕ¾³ÌÐòÒªÇó£¬Ò²ÊdzõѧÕßµÄÊÔÅ£µ¶¡£
ACCESSÊÇСÐÍÊý¾Ý¿â£¬¼ÈÈ»ÊÇСÐ;ÍÓÐËû¸ù±¾µÄ¾ÖÏÞÐÔ£º
......
ÔÎÄ´«ËÍÃÅ£ºhttp://topic.csdn.net/u/20091010/14/FC7737C1-D60B-43F1-A8B5-A9EEF2DE4426.html
¼ÙÈçÏÖÔÚÓÐÁ½ÕÅ±í£º
1.±ístuinfo
sid sname subs
1 jack |1|2|
2 marry |1|4|
3 tom |3|
2.±ísubinfo
subid subname
1 physics
2 &n ......
ÔÚжÔØVisual Studio 2008 ʱ£¬Ìí¼Ó/ɾ³ý³ÌÐò ÀïÃæÓÐÐí¶àµÄ°²×°Îļþ£¬´ËʱÈç¹ûûÓÐÏÈжÔØSQL Server 2005 £¬
¶øÏÈжÔØ.Net Framework£¨Ìáʾ£ºÐ¶ÔØ.net FrameworkµÄµÃ°²°æ±¾´Ó¸ßµ½µÍµÄ˳Ðò£©£¬
ÕâʱSQL Server 2005¾Í²»ÄÜжÔØ,ɾ³ýProgram files ÏÂÃæµÄ Microsoft SQL Server Îļþ¼ÐҲûÓÐ×÷Óá£
ÖØа²×°ÌáʾÊý¾Ý¿ ......