鍓嶈█鏈枃鏄leetcode涓婄殑涓€浜汼QL棰樼洰锛?4/19锛夌殑瑙ｆ瀽锛屾墍鏈夐鐩兘浣跨敤浜哅ySQL鐨勮娉曘€傝繖浜涢鐩殑SQL鍐欐硶鍙兘涓嶆槸鏈€浼樼殑锛屼絾鏄兘閫氳繃浜唋eetcode涓婄殑鎵€鏈夋祴璇曠敤渚嬨€傚鏋滀綘鏈夋洿濂界殑SQL锛岃鑱旂郴鎴戯紝馃惂锛?025873823銆俵eetcode涓婄殑SQL棰樹笉澶氾紝鍙湁19棰樸€備笉骞哥殑鏄紝鎴戞病鏈夋妸瀹冧滑閮芥媿涓嬫潵銆備互鍚庢垜浼氬敖閲忎竴涓€瑙ｇ瓟銆傞鐩竴锛氬悎骞朵袱寮犺〃鍚堝苟涓ゅ紶琛紝棰樼洰寰堢畝鍗曪紝涓昏鑰冨療JOIN璇硶鐨勪娇鐢ㄣ€傚敮涓€闇€瑕佹敞鎰忕殑鏄爣棰樹腑鐨勯偅鍙ヨ瘽锛屸€滄浜烘槸鍚︽湁鍦板潃淇℃伅鈥濄€傝鏄庡嵆浣縋erson琛ㄤ腑娌℃湁淇℃伅锛屾垜浠篃闇€瑕佽繑鍥濸erson琛ㄧ殑鍐呭銆傛墍浠ユ垜閫夋嫨浣跨敤宸﹀鏌ヨ锛屽綋鐒朵綘涔熷彲浠ラ€夋嫨RIGHTOUTERJOIN锛屽氨鐪嬩綘鎬庝箞鍐欐煡璇㈣鍙ヤ簡銆傚洖绛擲ELECTPerson.FirstName,Person.LastName,Address.City,Address.StateFROMPersonLEFTOUTERJOINAddressONPerson.PersonId=Address.PersonId闂2锛氱浜岄珮鐨勮柂姘寸浜岄珮鐨勮柂姘达紝杩欓鏈韩涓嶉毦锛屼絾鏄娉ㄦ剰鏍囬涓殑鎻忚堪鈥滃鏋滅浜岄珮鐨勮柂姘翠笉瀛樺湪锛屾煡璇㈠簲璇ヨ繑鍥瀗ull鈥濓紝鎰忔€濇槸濡傛灉SQL娌℃湁鏌ヨ鍒扮粨鏋滐紝SQL鏈韩闇€瑕佷竴涓粯璁ょ殑杩斿洖鍊笺€傛€庝箞鍔烇紝娌℃湁缁撴灉涔熻繑鍥炰竴涓€笺€傞€氳繃Google锛屾壘鍒颁簡銆怰eturningavalueevenifnoresult銆戠殑瑙ｅ喅鏂规锛坔ttps://stackoverflow.com/que....浣跨敤IFNULL鍑芥暟锛屽皢鏁存潯SQL璇彞浣滀负IFNULL鍑芥暟鐨勫弬鏁般€傚鏋淚FNULL鍑芥暟鍙峰鏋滃叾涓竴涓弬鏁颁负NULL锛屽垯杩斿洖IFNULL鍑芥暟鐨勭浜屼釜鍙傛暟锛屽惁鍒欒繑鍥炵涓€涓弬鏁般€傚洖绛擲ELECTIFNULL((SELECTEmployee.SalaryFROMEmployeeGROUPBYEmployee.SalaryORDERBYEmployee.SalaryDESCLIMIT1OFFSET1),NULL)ASSecondHighestSalary;绗笁棰橈細鍒嗘暟鎺掑悕鏈涓昏鑰冨療SQL鏌ヨ涓浣曠敓鎴愬簭鍙凤紝鍥犱负琛ㄦ湰韬笉鍖呭惈RANK瀛楁銆傛垜閫氳繃璋锋瓕鍦╯tackoverflow涓婃壘鍒颁簡绛旀锛屽湪mysql鏌ヨ涓敓鎴愬簭鍒楀彿銆傚湪鏌ヨ缁撴灉涓坊鍔犲簭鍙稟nswer#3.閫氳繃INNERJOINSELECTScores.Score,RANKINDEX.rankASRANKFROMScoresINNERJOIN(#2.灏唕anking娣诲姞鍒皊core琛ㄥ悗涓嶅幓閲?鎺掑簭鍘婚噸瀛楁(搴忓彿)SELECTRANK.ScoreASScore,@a:=@a+1rankFROM(#1.棣栧厛鎺掑簭鍘婚噸scoretableSELECTDISTINCTScores.ScoreFROMScoresORDERBYScores.ScoreDESC)RANK,(SELECT@a:=0)ASa)ASRANKINDEXONRANKINDEX.Score=Scores.ScoreORDERBYScores.ScoreDESC棰樼洰4锛氭敹鍏ヨ秴杩囩粡鐞嗘敹鍏ョ殑鍛樺伐杩欐槸涓€涓緢绠€鍗曠殑闂锛岃繖閲屽氨涓嶈В閲婁簡SELECTemp1.NameASEmployeeFROMEmployeeASemp1,EmployeeASemp2WHEREemp1.ManagerId=emp2.鈥嬧€婭dANDemp1.Salary>emp2.鈥嬧€婼alary闂5锛氭煡鎵鹃噸澶嶇殑鐢靛瓙閭欢鍦板潃涔熸槸涓€涓潪甯哥畝鍗曠殑闂銆傚敮涓€鍙兘闇€瑕佺悊瑙ｇ殑灏辨槸GROUPBYPerson.Email鐨勮瘽锛屽彲浠ヨ捣鍒癙erson.Email瀛楁鍘婚噸鐨勪綔鐢ㄥ洖绛擲ELECTPerson.EmailASEmailFROMPersonGROUPBYPerson.EmailHAVINGCOUNT(Person.Email)>1闂6锛氫粠涓嶄笅鍗曠殑瀹㈡埛杩樻槸涓€涓緢绠€鍗曠殑闂锛屼富瑕佽€冨療瀛愭煡璇㈢殑浣跨敤锛屽洖绛擲ELECTCustomers.NameASCustomersFROMCustomersWHERECustomers.IdNOTIN(SELECTOrders.CustomerIdFROMOrders)棰樼洰7锛氭渶楂樼殑鍛樺伐salaryinthedepartment鍦ㄥ洖绛旇繖涓棶棰樹箣鍓嶏紝閮ㄩ棬涓伐璧勬渶楂樼殑鍛樺伐銆傛垜浠渶瑕佹竻妤氬湴鐭ラ亾涓€浠朵簨銆傗€滈櫎鑱氬悎鍜岃绠楄鍙ュ锛孲ELECT璇彞涓殑姣忎竴鍒楅兘蹇呴』鍦℅ROUPBY瀛愬彞涓粰鍑衡€濄€傛崲鍙ヨ瘽璇达紝鎴戜滑鍦ㄦ眰鍚勪釜閮ㄩ棬鐨凪ax鏈€楂樿柂姘存椂锛屾槸鏃犳硶璁＄畻鍑哄憳宸ョ殑id鐨勩€傚浜庤繖涓棶棰橈紝鎴戜滑鍥炵瓟鐨勬楠ゅ垎涓轰袱姝ワ紝1.鎵惧埌姣忎釜閮ㄩ棬瀵瑰簲鐨勬渶楂樿柂姘达紝骞跺皢缁撴灉瀛樺偍涓烘淳鐢熻〃2.鏍规嵁鍛樺伐鐨勯儴闂╥d锛屼互鍙婂憳宸ョ殑钖按锛屼笌娲剧敓琛紝姣旇緝瀵瑰簲鍛樺伐鐨勫伐璧勬槸鍚︾瓑浜庢淳鐢熻〃涓儴闂ㄧ殑鏈€楂樺伐璧勩€傚鏋滅浉绛夛紝鍒欐浜虹殑钖按涓洪儴闂ㄦ渶楂樿柂姘淬€傝В鍐虫柟妗圫ELECTDepartment.NameASDepartment,Employee.NameASEmployee,Employee.SalaryASSalaryFROMEmployeeINNERJOINDepartmentINNERJOIN(#绗竴姝ュ厛鎵惧嚭姣忎釜閮ㄩ棬鐨勬渶楂樺伐璧勶紝骞朵綔涓烘淳鐢熻〃SELECTMax(Employee...DepartmentBigSalary.DepartmentId#姣旇緝瀵瑰簲鍛樺伐鐨勮柂姘存槸鍚︾瓑浜庢淳鐢熻〃涓儴闂ㄧ殑鏈€楂樿柂姘碬HEREEmployee.Salary=DepartmentBigSalary.Salary棰樼洰8锛氬垹闄ら噸澶嶉偖浠朵笉鎸囧畾DELETE璇彞浣跨敤WHERE瀛愬彞鏃讹紝榛樿鏄垹闄よ〃涓殑鎵€鏈夎銆倀opic鎸囧畾浜嗕袱涓潯浠讹紝鈥滃垹闄erson琛ㄤ腑鎵€鏈夐噸澶嶇殑閭欢锛屽彧淇濈暀閲嶅閭涓璉d鏈€灏忕殑鈥濓紝WHERE涔熼渶瑕佹寚瀹氫袱涓潯浠躲€備袱绉嶆儏鍐碉紝璇峰弬鑰冧笅闈㈢殑浠ｇ爜銆傚敮涓€闇€瑕佹敞鎰忕殑鏄疍ELETE鏈韩鏄竴涓洿鏂版搷浣滐紝鎵€浠ラ渶瑕佸湪FROM涓柊寤轰竴涓淳鐢熻〃锛屽惁鍒欎細鎶ラ敊锛堜笉鑳藉湪FROM瀛愬彞涓寚瀹氭洿鏂扮殑鐩爣琛?Person'锛夊洖绛擠ELETEFROMPersonWHEREPerson.EmailIN(#鏉′欢1锛氬垹闄ら暱搴﹀ぇ浜?鐨勮SELECTtable1.EmailFROM(SELECTPerson.EmailASEmailFROMPersonGROUPBYPerson.EmailHAVINGCOUNT(Person.Email)>1)AStable1)ANDPerson.IdNOTIN(#鏉′欢涓€锛氬垹闄ら暱搴﹀ぇ浜?鐨勮锛屼絾涓嶅寘鎷琲d鏈€灏忕殑琛孲ELECTtable2.idFROM(SELECTMIN(Person.Id)ASidFROMPersonGROUPBYPerson.EmailHAVINGCOUNT(Person.Email)>1)AStable2)绗?棰橈細鍗囨俯杩欓亾棰樹富瑕佽€冨療self-join鐨勪娇鐢ㄣ€傚浣曞垽鏂浉閭讳袱涓猂ecordDate鐨凾emperature澶у皬锛熼€氳繃鍚岃〃鐨凧OIN杩炴帴锛屼慨鏀笿OIN鐨凮N鏉′欢涓簑1.RecordDate=DATE_SUB(w2.RecordDate,INTERVAL-1DAY)锛寃1琛ㄧ殑RecordDate涓簑2琛ㄧ殑RecordDate鐨勫墠涓€澶?鑰寃2涓巜1鐨勬瘡涓€琛岀浉鍏宠仈锛屾瘡涓€琛屽疄闄呬笂鏄痺1涔嬪悗鐨勯偅涓€澶┿€傚洖绛擲ELECTw1.IdASIdFROMWeatherASw1INNERJOINWeatherASw2ONw1.RecordDate=DATE_SUB(w2.RecordDate,INTERVAL-1DAY)WHEREw1.Temperature>w2.Temperature闂10锛氬浜庝竴涓ぇ鍥芥潵璇达紝杩欐槸涓€涓潪甯哥畝鍗曠殑闂锛屾澶勪笉鍐嶉噸澶嶇瓟妗圫ELECTWorld.NameASName,World.populationASpopulation,World.areaASareaFROMWorldWHEREWorld.population>25000000ORWorld.area>3000000闂11锛欳lasseswithmorethan5studentsClasseswithmore5涓悓瀛︿互涓婏紝鏈鐨勯噸鐐规槸瀵笹ROUPBY鐨勫幓閲嶆晥鏋滅殑璁ょ煡銆傞鍏堬紝瀛愭煡璇娇鐢ㄥ祵濂楀垎缁勩€傚厛鐢ㄨ绋嬪垎缁勶紝鍐嶆寜瀛︾敓鍒嗙粍銆傝兘鏈夋晥鍘婚櫎璇剧▼銆佸鐢熼噸澶嶆帓琛屻€備负浠€涔堜笉鐩存帴浣跨敤瀛︾敓鍒嗙粍鍛紵鍥犱负杩欐牱鍋氫細涓㈠け瀛︾敓鐨勮绋嬩俊鎭€傚灞傛煡璇㈠彧闇€瑕佹煡鎵綜OUNT澶т簬5鐨勮绋嬨€傚洖绛擲ELECTClassLength.classFROM(#鎺掗櫎瀛︾敓鍜岃绋嬮噸澶嶇殑琛孲ELECTcourses.classASclassFROMcoursesGROUPBYcourses.class,courses.student)ASClassLengthGROUPBYClassLength.classHAVINGCOUNT(ClassLength.class)>=5棰?2锛氭湁瓒ｇ殑鐢靛奖杩欎釜track鐨勯鐩篃姣旇緝绠€鍗曘€傝€冨療鐨勯噸鐐规槸鍗曞弻鏁扮殑鍒ゆ柇銆傛垜浠彲浠ヤ娇鐢∕ySQL鐨凪OD鍔熻兘銆侻OD(N,M),MOD鍑芥暟灏嗚繑鍥濶/M鐨勪綑鏁癰oring'ANDMOD(cinema.id,2)=1ORDERBYratingDESC闂13锛氫氦鎹㈠伐璧勯棶棰樻湰韬渶瑕佹洿鏂版煡璇紝涓嶉渶瑕佷腑闂翠复鏃惰〃銆傚洜姝わ紝鍦⊿QL涓紝閫昏緫鍒ゆ柇鏄笉鍙伩鍏嶇殑銆傝繖閲屼娇鐢∕ySQl鐨凜ASEWHEN璇彞鏉ュ洖绛擴PDATEsalarySETsalary.sex=(CASEWHENsalary.sex='m'THEN'f'WHENsalary.sex='f'THEN'm'ELSE'sex'END)棰樼洰14锛氳繛缁嚭鐜扮殑鏁板瓧绫讳技浜庘€滄俯搴︿笂鍗団€濈殑棰樼洰銆傚悎鐞嗕娇鐢ㄨ嚜杩炴帴鍙互瑙ｅ喅杩欎釜闂銆係ELECTConsecutive.ConsecutiveNumsFROM(SELECTl1.NumASConsecutiveNumsFROMLogsASl1INNERJOINLogsASl2INNERJOINLogsASl3ONl1.id=l2.id-1ANDl2.id=l3.id-1ANDl1.id=l3.id-2WHEREl1.Num=l2.NumANDl2.Num=l3.NumANDl1.Num=l3.Num)ASConsecutiveGROUPBYConsecutive.ConsecutiveNums
