How to directly use HAVING statements without GROUPBY in MySQL _ MySQL

This article describes how to use the HAVING statement directly in MySQL without GROUPBY, and how to use the MAX and MIN functions in this case, if you need a table, you can refer to a response from someone who gave me today. The Table id is the primary key. in this way, a record can be returned:

  “SELECT * FROM t HAVING id=MIN(id);”

However, if you replace MIN with MAX, the returned result is null:

  “SELECT * FROM t HAVING id=MAX(id);”

Why?

Let's test this situation first.

This is the table structure, Initialize two records, and then test:

root@localhost : plx 10:25:10> show create table t2G*************************** 1. row ***************************    Table: t2Create Table: CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 root@localhost : plx 10:25:15> select * from t2;+------+----+| a  | id |+------+----+|  1 | 1 ||  1 | 3 |+------+----+2 rows in set (0.00 sec) root@localhost : plx 10:25:20> SELECT * FROM t2 HAVING id=MIN(id);+------+----+| a  | id |+------+----+|  1 | 1 |+------+----+1 row in set (0.00 sec) root@localhost : plx 10:25:30> SELECT * FROM t2 HAVING id=MAX(id);Empty set (0.00 sec)

At first glance, it seems like this. why?

I will try again, change field a to 10, and then try field:

root@localhost : plx 10:26:58> select * from t2;+------+----+| a  | id |+------+----+|  10 | 1 ||  1 | 3 |+------+----+2 rows in set (0.00 sec) root@localhost : plx 10:28:20> SELECT * FROM t2 HAVING a=MAX(a);+------+----+| a  | id |+------+----+|  10 | 1 |+------+----+1 row in set (0.00 sec) root@localhost : plx 10:28:28> SELECT * FROM t2 HAVING a=MIN(a);Empty set (0.00 sec)

I wiped it. this time, MAX can return data, and MIN can't. why?

Narration

Generally, HAVING clauses are used in combination with group by clauses. HAVING alone does not comply with the rules,

However, MySQL will rewrite the statement and add a group by null, "SELECT * FROM t HAVING id = MIN (id) "will be rewritten to" SELECT * FROM t group by null having id = MIN (id) ", so that the syntax will conform to the specification.

Continue ......

However, what results will this group by null produce? After checking the code and experiment, we can prove that group by null is equivalent to LIMIT 1:

root@localhost : plx 10:25:48> SELECT * FROM t2 GROUP BY NULL;+------+----+| a  | id |+------+----+|  10 | 1 |+------+----+1 row in set (0.00 sec)

That is to say, after group by is null, there will be only one GROUP, which is the first row of data.

However, in this case, the results of MIN and MAX should be consistent, so we should not have a result for MAX and MIN, but no result for me. why is this? let's do another test.

Modify the data and view the MIN/MAX values directly:

root@localhost : plx 10:26:58> select * from t2;+------+----+| a  | id |+------+----+|  10 | 1 ||  1 | 3 |+------+----+2 rows in set (0.00 sec) root@localhost : plx 10:27:04> SELECT * FROM t2 GROUP BY NULL;+------+----+| a  | id |+------+----+|  10 | 1 |+------+----+1 row in set (0.00 sec) root@localhost : plx 10:30:21> SELECT MAX(a),MIN(a),MAX(id),MIN(id) FROM t2 GROUP BY NULL;+--------+--------+---------+---------+| MAX(a) | MIN(a) | MAX(id) | MIN(id) |+--------+--------+---------+---------+|   10 |   1 |    3 |    1 |+--------+--------+---------+---------+1 row in set (0.00 sec)

Did you find the problem?

The value of the MAX/MIN function is global, rather than in the LIMIT 1 group.

Therefore, when group by is NULL, the MAX/MIN function obtains the maximum and minimum values of all data!

So, "SELECT * FROM t HAVING id = MIN (id)" is essentially "SELECT * FROM t HAVING id = 1", you can return a record, in essence, "SELECT * FROM t HAVING id = MAX (id)" is "SELECT * FROM t HAVING id = 3". of course no records are returned, which is the root cause of the problem.

Test group by a. That's right. each GROUP has only one row, so MAX/MIN is as large. this is the maximum and minimum values in the GROUP.

root@localhost : plx 11:29:49> SELECT MAX(a),MIN(a),MAX(id),MIN(id) FROM t2 GROUP BY a;+--------+--------+---------+---------+| MAX(a) | MIN(a) | MAX(id) | MIN(id) |+--------+--------+---------+---------+|   1 |   1 |    3 |    3 ||   10 |   10 |    5 |    5 |+--------+--------+---------+---------+2 rows in set (0.00 sec)

When group by is null, MAX/MIN behavior is the essence of this problem. therefore, try to use standard syntax, be sure to check whether its behavior is consistent with what it understands.