<p><strong>Misunderstanding:</strong></p><p>Don't be wrong, think HAVING and Group BY must be used with it.</p><p><strong>Analysis of where and having usage:</strong></p><p><strong><br/></strong></p><p>1. WHERe and having can use scenes:</p><p>Select goods_price, goods_name from goods where goods_price> 100</p><p>Select goods_price, goods_name from goods having goods_price> 100</p><p>Explanation: The premise of having above is that I have screened the goods_price field, and the effect of where is in this case is equivalent.</p><p>
But if there is no select goods_price, an error will be reported! Intersection Because Haveing is screened from the previous screening field, and where is directly screened from the field in the data table.</p><p>
Therefore, you can see that where is aimed at the database file, and HAVING played for the results set.</p><p>
In fact, the second SQL statement is equivalent to the following statements to better understand:</p><p>
Select goods_price, goods_name from goods where 1 having goods_price> 100</p><p>
</p><p>
2. You can only use where, and you cannot use the situation of having:</p><p>Select goods_name, goods_number from goods where goods_price> 100</p><p>Select goods_name, goods_number from goods having goods_price> 100</p><p>Explanation: The second SQL statement reports an error. This is caused by the screening of goods_price. It can be seen that HAVING plays a role in the result set.</p><p>
</p><p>
3. You can only use having, and you do not use where:</p><p>
Query the average price of each Category_id product, and obtain product information with an average price greater than 1,000 yuan.</p><p>
Select Category_id, AVG (GOODS_PRICE) As Ag from Goods Group by Category_id Having AG> 1000</p><p>
Select Category_id, AVG (GOODS_PRICE) As Ag from Goods WHERE AG> 1000 Group by Category_id_id_id</p><p>
Explanation: The second SQL statement reports an error, because there is no AG field in the data table of FROM Goods.</p><p><strong>Review:</strong></p><p>
WHERE must follow the fields in the data table, where is the role of database files.</p><p>
Haveing only query based on the results set queries before, so Haveing plays a role in the results set.</p><p><strong>The comprehensive application case of where and having:</strong></p><p><span style="color: rgb(45, 48, 55); font-family: "Helvetica Neue", Helvetica, Arial, "Microsoft Yahei", "Hiragino Sans GB", "Heiti SC", "WenQuanYi Micro Hei", sans-serif; background-color: rgb(255, 255, 255);">SELECT st_name,sum(score<60) as k,avg(score) FROM tbl_score group by st_name having k>=2；</span></p><p>
</p>
