SQL 笔记

mysql> SELECT * FROM test_tb;+----+---------+---------+------------+---------------------+--------+---------+| id | name    | address | birthday   | created_at          | height | team_id |+----+---------+---------+------------+---------------------+--------+---------+|  1 | li      | panshui | 1992-04-01 | 1992-06-01 00:00:00 |    170 |       1 ||  2 | shen    | NULL    | 1993-09-01 | 2021-10-01 00:00:00 |    165 |       1 ||  3 | shuange | NULL    | NULL       | NULL                |    190 |       2 ||  4 | yaoming | NULL    | NULL       | NULL                |    200 |       2 ||  5 | shabi   | NULL    | NULL       | NULL                |    160 |       2 |+----+---------+---------+------------+---------------------+--------+---------+5 rows in set (0.00 sec)
-- will error if height not in SELECTmysql> SELECT name FROM test_tb GROUP BY team_id HAVING height > AVG(height);ERROR 1054 (42S22): Unknown column 'height' in 'having clause'
-- executable, but the result is incorrect.mysql> SELECT name,height FROM test_tb GROUP BY team_id HAVING height > AVG(height);+---------+--------+| name    | height |+---------+--------+| li      |    170 || shuange |    190 |+---------+--------+2 rows in set (0.00 sec)
-- avg result is correct.mysql> SELECT name, height, AVG(height), team_id FROM test_tb GROUP BY team_id HAVING height > AVG(height);+---------+--------+-------------+---------+| name    | height | AVG(height) | team_id |+---------+--------+-------------+---------+| li      |    170 |    167.5000 |       1 || shuange |    190 |    183.3333 |       2 |+---------+--------+-------------+---------+2 rows in set (0.03 sec)
-- correct results with child query.mysql> SELECT name, height, team_id FROM test_tb as a WHERE height > (SELECT AVG(height) FROM test_tb AS b WHERE b.team_id = a.team_id)    -> ;+---------+--------+---------+| name    | height | team_id |+---------+--------+---------+| li      |    170 |       1 || shuange |    190 |       2 || yaoming |    200 |       2 |+---------+--------+---------+3 rows in set (0.01 sec)
-- wrong result when using aggregating function in SELECT.mysql> SELECT name, AVG(height), team_id FROM test_tb as a WHERE height > (SELECT AVG(height) FROM test_tb AS b WHERE b.team_id = a.team_id)    -> ;+------+-------------+---------+| name | AVG(height) | team_id |+------+-------------+---------+| li   |    186.6667 |       1 |+------+-------------+---------+1 row in set (0.00 sec)