2.7. 蕔镖庘昄

启崓崔昄噽垄噿係昄扞康库巕婔湙PostgreSQL 櫇毕蕔镖庘昄㔗婔婻蕔镖庘昄傯崔婻膷噖臯婺螇䞖庺婔婻䂷悩㔗懫套潏傸橬婘婔婻臯镖劽婪螇䞖 count(昄䕞), sum(攂启), avg(庺唚), max(橔崓唚), min(橔償唚)䔇庘昄㔗

懫套潏傸埇傖䫘婋麵䔇臺埖欆庺欔橬嘯橷婺䔇橔醻橷庥

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

套悩潏傸愿䘖長臖臂昄埏䫘婘巻婻嘯婗埇脘嚔䫘

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     -- 髍

婉誺認婻桹濘婉脘誊蘸啹婺蕔镖庘昄 max 婉脘䫘庯 WHERE 床埖婺㔗庻婘認婻鍊彽滇啹婺 WHERE 床埖喿垔巻底臯埇傖誕噖蕔镖黽枕啹溴垄媙驔婘蕔镖庘昄幋嬉螇䞖㔗婉誺潏傸埇傖䫘噽垄桹濘垂䯄認婻䕞䔇認麯潏傸嘪䫘床昖臵

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

認湙啔滇埇傖䔇啹婺床昖臵滇婔渇䋸䆋䔇螇䞖垄䋸䆋庯崡北昖臵螇䞖躻噌䔇蕔镖㔗

蕔镖劯湙幘婩䫘庯 GROUP BY 床埖㔗懫套潏傸埇傖诙埡懟婻嘯婗嘯橷䔇橔醻唚

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | max
---------------+-----
 Hayward       |  37
 San Francisco |  46
(2 rows)

認湙懟婻嘯婗婔婻膷庺㔗懟婻蕔镖䂷悩鄘滇婘对陉臖嘯婗䔇臯婪麵螇䞖䔇㔗潏傸埇傖䫘 HAVING 誺悴認底彖䂇

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | max
---------+-----
 Hayward |  37
(1 row)

認湙儌埻䂍庺闼底 temp_lo 唚敆䂟橬嘯庯 40 庥䔇嘯婗㔗橔劯套悩潏傸埻噿媄闼底劉庖傖"S"嚔崘䔇嘯婗潏傸埇傖䫘

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'
    GROUP BY city
    HAVING max(temp_lo) < 40;

臺埖婺䔇 LIKE 欓臯昇嚟对陉婘誗9.7麯橬蓼麪㔗

䊖蓼蕔镖启 SQL 䔇 WHERE 启 HAVING 床埖幋閘䔇噿係麂婩麉襕㔗WHERE 启 HAVING 䔇嘺橸寺彆套婋WHERE 婘彖䂇启蕔镖螇䞖幋嬉锬埡膷噖臯(垄毓彽巻底臯誕噖蕔镖螇䞖)蔯 HAVING 婘彖䂇启蕔镖幋劯锬埡膷庺臯㔗啹溴WHERE 床埖婉脘寙劆蕔镖庘昄啹婺臘商䫘蕔镖庘昄彴桺闼底臯儖襕膷噖䂍蕔镖誊䞖滇澇橬懟幬䔇㔗䕩埉HAVING 床埖攂滇寙劆蕔镖庘昄㔗嘷䇽嘹埇傖喍婉嘪䫘蕔镖䔇 HAVING 床埖嘖認湙啔澇傔幽喘崇啹婺劯湙䔇溇傽埇傖敘橬昽婄䫘庯 WHERE 黽枕㔗

婘嬉麵䔇冋床麯潏傸埇傖婘 WHERE 麯庫䫘嘯婗劉䓄鍊彽啹婺垄婉驔襕蕔镖㔗認湙懫婘 HAVING 麯嵂媹鍊彽敘媹醻昽啹婺潏傸镪噉庖婺闼底橻锔誺 WHERE 演昖䔇臯誕臯彖䂇启蕔镖螇䞖㔗