ÄãÊÇ·ñÔø¾ÎªÁ˵õ½ËùÐèÒªµÄÐÅÏ¢¶ø·´¸´²éѯ£¿×ÓÑ¡Ôñ£¬Ò²±»³ÆÎª×Ó²éѯ£¬Ò²ÐíÕýÊÇÄãÔÚѰÕҵġ£SQLµÄÕâÏÄÜʹÄã¿ÉÒÔÔÚÒ»×é½á¹ûÖвéѯ£¬´´ÔìÐԵظø½á¹û×é¼ÓÒÔÏÞ¶¨£¬»òÊÇÔÚÏòÊý¾Ý¿âµÄµ¥Ò»µ÷ÓÃÖн«½á¹ûÓëÒ»¸öÎÞ¹ØÏµµÄ²éѯ×öÏà¹Ø¡£ÕâÆªÎÄÕÂÖÐÎÒ½«¸ø³ö¼¸¸ö×ÓÑ¡ÔñµÄÀý×Ó²¢¾ÍºÎʱʹÓÃËûÃǽøÐÐÌÖÂÛ¡£
ÔÚÒ»¸ö½á¹û×éÖÐËÑË÷
×ÓÑ¡ÔñµÄÀíÄîºÜ¼òµ¥£ºÒ»¸öÑ¡Ôñ²éѯ°²ÖÃÔÚÁíÒ»¸ö²éѯÄÚ²¿£¬´´½¨Ò»¸öÔÚµ¥Ò»ÉùÃ÷ËÑË÷Öв»¿ÉÓõÄ×ÊÔ´¡£×ÓÑ¡ÔñÔÊÐí²éѯµÄºÍ²¢£¬½á¹û×é±È½ÏµÄÔðÈÎÂäµ½ÁËÊý¾Ý¿âÖжø²»ÊÇÓ¦ÓÃÈí¼þ´úÂëÖС£
ʹÓÃÕâ¸ö¹¦ÄܵÄÒ»¸ö;¾¶ÊǶÔÁ½¸ö±í¸ñÖеĿɱÈÊý¾ÝרÀ¸ÖеÄÖµ½øÐж¨Î»¡£ÀýÈ磬ÎÒµÄÒ»¸öÊý¾Ý¿âÓÐÁ½¸ö±í¸ñ£¬AlbumºÍLyric¡£ÎÒ¿ÉÒÔºÜÈÝÒ×µØÍ¨¹ýÏÂÃæµÄ×Ó²éѯÉùÃ÷À´ÕÒµ½Ã¿Ò»¸öMetallicaµÄ¸èÇúÖаüº¬¡°justice¡±µÄ¸èÃû£º
SELECT song_name FROM Album
WHERE band_name = ¡®Metallica¡¯
AND song_name IN
(SELECT song_name FROM Lyric
WHERE song_lyric LIKE ¡®%justice%¡¯);
Õâ¸öÀý×ÓÊǺܼòµ¥µÄ£¬ÎÒ´ÓAlbum±í¸ñÖÐÑ¡ÔñÁËËùÓÐMetallicaµÄ¸èÇú£¬½Ó×Å£¬ÎÒÔÚlyric±í¸ñÖÐÑ¡ÔñËùÓаüº¬¡°justice¡±µÄ¸èÇú£¬×îºó£¬ÎÒʹÓÃIN¹Ø¼ü×ÖÀ´´ÓLyric±í¸ñ½á¹û×éÖÐÏÔʾµÄAlbum±í¸ñÖзµ»Ø¸èÇúÃû³Æ¡£
ÎÒʹÓÃLyric±í¸ñ½á¹û×éÀ´¸øAlbum±í¸ñÖеĽá¹û×öÏÞ¶¨¡£WHERE×Ó¾äÖеÄ×ÓÑ¡Ôñ²¿·ÖÊÇÍêÈ«×Ô°üº¬µÄ£¬Òò´ËÎÒ²»ÐèҪʹÓÃÀýÈçAlbum.song_nameºÍLyric.song_nameµÈÍêÕûµÄרÀ¸Ãû³Æ¡£ÎÒûÓдÓ×îÖÕ½á¹û×éµÄLyric±í¸ñÖзµ»ØÈκÎÖµ£¬Èç¹ûÎÒÐèÒª¸èÇúµÄLyric£¬ÎÒ»áʹÓÃÒ»¸öJOINÉùÃ÷¡£
ʹÓÃNOT INÅųý½á¹û
Äã¿ÉÒÔʹÓÃNOT IN¹Ø¼ü×ÖÀ´»ñµÃÃ÷È·µØ²»±»°üº¬ÔÚÁíÒ»¸ö½á¹û×éÖеĽá¹û¡£ÀýÈ磬ÎÒÏëҪͨ¹ýÏÂÃæµÄ´úÂëÀ´·µ»ØMetallicaÔÚ¡°And Justice for All¡±×¨¼Öв»°üº¬µ¥´Ê¡°justice¡±µÄ¸èÇú£º
SELECT song_name FROM Album
WHERE album_name = ¡®And Justice for All¡¯
AND band_name = ¡®Metallica¡¯
AND song_name NOT IN
(SELECT song_name FROM Lyric
WHERE song_lyric LIKE ¡®%justice%¡¯);
ÔÚÇ°ÃæµÄSQL´úÂëÖУ¬ÎÒÑ¡ÔñÁËMetallicaµÄ¡°And Justice for All,¡±×¨¼ÖеÄËùÓиèÇú£¬½Ó×ÅÊÇ´øÓиè´ÊÖдøÓС°justice¡±ËùÓиèÇú£¬×îºó´ÓÔÚLyric½á¹û×éÖÐûÓгöÏÖµÄAlbum½á¹û×é·µ»ØÁËËùÓиèÇú¡£½ÏÖ®ÓÚ·µ»ØÁ½¸ö²éѯ²¢Ê¹ÓôúÂëÀ´±È½ÏÊý×飬Äãͨ¹ýÒ»¸öµ¥¶ÀµÄÉùÃ÷¾Í¿ÉÒԵõ½È·ÇеĽá¹û¡£
ʹÓÃEXISTSÀ´Ïà¹Ø½á¹û
ÓÐʱÄã¿ÉÒÔͨ¹ý¶àÖÖ;¾¶À´·ÃÎÊÏàͬµÄÊý¾Ý£¬¶øÇÒÄãÐèÒª¶ÔÄãµÄ½á¹û½øÐÐÆ¥Å䣨»òÏà¹Ø£©À´µÃµ½ÖµµÄ½»²æÇø¡£ÀýÈ磬ÎÒ¿ÉÒÔͨ¹ýËÑË÷Album±í¸ñÀ´µÃµ½MetallicaµÄ¸èÇúÁÐ±í£¬¿ÉÊÇ£¬ÎÒÒ²¿ÉÒÔ´ÓÎÒµÄCover±í¸ñÖеõ½ÓÉDamage, Inc±íÑݵÄMetallicaµÄ¸èÇúµÄÁÐ±í£¬ÎÒ¿ÉÒÔÔÚÁ½¸ö±í¸ñÖÐÖ±½Ó±È½Ï²éѯ½á¹ûÀ´¶ÔÖµ×÷Ïà¹Ø¡£
SELECT Album.song_name FROM Album
WHERE Album.band_name = ¡®Metallica¡¯
AND EXISTS
(SELECT Cover.song_name FROM Cover
WHERE Cover.band_name = ¡®Damage, Inc.¡¯
AND Cover.song_name = Album.song_name);
ÔÚSQL´úÂëÖУ¬ÎÒʹÓÃÍêÕûµÄרÀ¸Ãû³Æ£¬ÕâÊÇÒòΪÎÒÖ±½Ó¶ÔÁ½¸ö±í¸ñ×÷±È½Ï£¬¶ø²»½ö½öÊǽ«½á¹û×é×÷Ϊһ¸ö±»¶¯×ÊÔ´À´Ê¹Óá£ÎÒ²¢²»´ÓCover±í¸ñÖзµ»Ø½á¹û¡£Ò»Ð©Êý¾Ý¿âÖ§³ÖNOT EXISTS¹Ø¼ü×ÖÀ´È·±£Ä㲢ûÓÐÆ¥Åä¡£
ʹÓúͼƺ¯ÊýÀ´±È½Ï
³ýÁËʹÓÃ×ÓÑ¡ÔñÔÚÏà¹ØµÄ±í¸ñÖмì²éÊý¾Ý£¬Ä㻹¿ÉÒÔÔÚÒ»¸öWHERE×ÓÑ¡ÔñÖÐʹÓúͼƺ¯ÊýÀ´È·¶¨Ö÷½á¹û×é¡£ÀýÈ磬ÎÒÏëÒªºËʵÿһ¸öMetallica¸èÇúÔÚAlbum±í¸ñÖеÄÌõÄ¿¡£¶øÇÒ£¬ÎÒ»¹Ïë·µ»ØÈ±ÉÙ¸èÇúµÄר¼µÄÃû³Æ¡£ºÜ·½±ãµØ£¬AlbumInfo±í¸ñ°üº¬µÄÒ»¸öרÀ¸(album_tracks)¸ø³öÁËÓ¦¸ÃÓжàÉÙÊ׸èÇú·½ÃæµÄÐÅÏ¢¡£
SELECT AlbumInfo.album_name FROM AlbumInfo
WHERE AlbumInfo.band_name = ¡®Metallica¡¯
AND album_tracks <>
(SELECT COUNT(*) FROM Album
WHERE Album.album_name = AlbumInfo.album_name);
ÏÖÔÚÎÒÒѾ³É¹¦µØ·µ»ØÁËËùÓÐMetallicaµÄר¼ÖУ¬Ó¦ÓеÄÇúÄ¿ÊýÁ¿ÓëAlbum±í¸ñÖÐʵ¼ÊµÄ¸èÇúÌõÄ¿ÊýÁ¿²»·ûµÄר¼Ãû³Æ¡£
[1] [2] ÏÂÒ»Ò³