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ÊÇÏȰѲ»·ûºÏÌõ¼þµÄ¼Ç¼¹ýÂ˺ó²Å½øÐÐͳ¼Æ£¬Ëü¾Í¿ÉÒÔ¼õÉÙÖмäÔËËãÒª´¦ÀíµÄÊý¾Ý£¬°´Àí˵Ӧ¸ÃËÙ¶ÈÊÇ×î¿ìµÄ¡£
Ïà¹ØÎĵµ£º
Êý¾Ý¿âsqlÓï¾äϵÁж¼ÊÇÒÔÏÂÃæµÄ±íΪ»ù´¡µÄ
1. д³ö²½Öè4Öеĸ÷Ïî²Ù×÷µÄSQLÓï¾ä¡£
¢Ù ¸øѧÉú±íÔö¼ÓÒ»ÊôÐÔNation£¨Ãñ×壩£¬Êý¾ÝÀàÐÍΪVarchar£¨20£©£»
alter table Student add Nation varchar(20);
¢Ú ɾ³ýѧÉú±íÖÐÐÂÔöµÄÊôÐÔNation£»
alter table Student DR ......
select datediff(month,'2009-11-02','2009-12-01')
²»ÖªµÀÄãÒªÔõô¸öÔ²îÒì
ÉÏÃæµÄÃüÁî½á¹ûÒ²ÊÇ1
µ«ÊÇÆäʵ°´ÕÕ³£Ê¶²îÒì²»ÊÇÒ»¸öÔ£¬ÊDzîÒ»ÌìÒ»¸öÔÂ
ÕâÓжÎÏֳɵĴúÂë¿ÉÒÔ°ïÖúÄãËã³ö³£Ê¶ÉϵIJîÒì
SQL code
declare @t table(a datetime,b datetime);
insert @t
select '2009-11-02','2009-12-01' UNION ......
where 1=1ÓÐʲôÓã¿ÔÚSQLÓïÑÔÖУ¬Ð´Õâôһ¾ä»°¾Í¸úûдһÑù¡£
select * from table1 where 1=1Óëselect * from table1ÍêȫûÓÐÇø±ð£¬ÉõÖÁ»¹ÓÐÆäËûÐí¶àд·¨£¬1<>2£¬'a'='a','a'<>'b'£¬ÆäÄ¿µÄ¾ÍÖ»ÓÐÒ»¸ö£¬whereµÄÌõ¼þΪÓÀÕ棬µÃµ½µÄ½á¹û¾ÍÊÇδ¼ÓÔ¼ÊøÌõ¼þµÄ¡£
ÔÚSQL×¢Èëʱ»áÓõ½Õâ¸ö£¬ÀýÈçselect * from table1 ......
΢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý(clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý)ºÍ·Ç¾Û¼¯Ë÷Òý(nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý)……
¡¡¡¡(Ò»)ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý(clustered index£¬Ò²³Æ ......
ÎÊÌâÒ»¡¢Íü¼ÇÁ˵ǼMicrosoft SQL Server 2005 µÄsaµÄµÇ¼ÃÜÂë
½â¾ö·½·¨£ºÏÈÓÃwindowsÉí·ÝÑéÖ¤µÄ·½Ê½µÇ¼½øÈ¥£¬È»ºóÔÚ‘°²È«ÐÔ’-‘µÇ¼’-ÓÒ¼üµ¥»÷‘sa’-‘ÊôÐÔ’£¬ÐÞ¸ÄÃÜÂëµã»÷È·¶¨¾Í¿ÉÒÔÁË¡£
ÎÊÌâ¶þ¡¢Òѳɹ¦Óë·þÎñÆ÷½¨Á¢Á¬½Ó£¬µ«ÊÇÔڵǼ¹ý³ÌÖз¢Éú´íÈ¡¡££¨provider:¹ ......