Probing into the problem of directly using the having statement without group by in Mysql _mysql

Source: Internet
Author: User

Today, a classmate gave me a response, there is a table, ID is the primary key, so that the wording can return a record:

  "SELECT * FROM T has id=min (ID);"

But instead of changing min to Max, the return is empty:

  "SELECT * FROM T has Id=max (ID);"

What is this for?

Let's start with a test to verify the situation.

This is the table structure, initialize two records, and then experiment:

ROOT@LOCALHOST:PLX 10:25:10> Show CREATE TABLE t2g
*************************** 1. Row ************************* * *
    table:t2
Create table:create Table ' T2 ' (
 ' a ' int () DEFAULT null,
 ' id ' int (a) unsigned not NULL AU To_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 * to T2 having id=min (ID) 
   +------+----+
| a  | id |
+------+----+
|  1 | 1 |
+------+----+
1 row in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:25:30> SELECT * to T2 having Id=max (ID) 
   
    empty Set (0.00 sec)


   

At first glance, it seems to be really like this, how can this be?

I'll try again, change the A field to 10, and then try the A 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 * to T2 having A=max (a);
+------+----+
| a  | id |
+------+----+
|  10 | 1 |
+------+----+
1 row in Set (0.00 sec)
 
ROOT@LOCALHOST:PLX 10:28:28> SELECT * to T2 having a=min (a);
Empty Set (0.00 sec)

I rub, this time Max can return, Min can not, this is why?

Narrator

In general, having clauses are used in conjunction with group by, and using the having itself is not in conformity with the specification,

But MySQL will do an override, plus a GROUP by NULL, "SELECT * from T has id=min (ID)" will be rewritten as "select * from T GROUP by NULL have id=min (ID)", This syntax is in line with the specification.

Go on......

But what happens to this GROUP by NULL? By looking at the code and experimenting, you 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, after group by NULL, there is only one group, with the first row of data in it.

But if so, Min, max results should be consistent, it should not be max and min a result, a no results ah, this is why, then do a test.

Modify the data and view the Min/max value 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 * to 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) FR OM T2 GROUP by NULL;
+--------+--------+---------+---------+
| MAX (a) | MIN (a) | MAX (ID) | MIN (ID) |
+--------+--------+---------+---------+
|   Ten |   1 |    3 |    1 |
+--------+--------+---------+---------+
1 row in Set (0.00 sec)

Did you find the problem?

The Max/min function value is global, not limit 1 within the group.

Therefore, when group by NULL, the Max/min function is to take the maximum and minimum values in all the data!

So, "SELECT * from T has id=min (ID)" is essentially "select * from t have Id=1″, can return a record, and" select * from t have Id=max (ID) "essentially "SELECT * from T has Id=3″, of course, does not return records, which is the root of the problem."

Test group by a, which is right, each group has only one row, so max/min is the same size, this time to get 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 |
|   Ten |   Ten |    5 |    5 |
+--------+--------+---------+---------+
2 rows in Set (0.00 sec)

The max/min behavior of GROUP by NULL is the nature of the problem, so, try to use standard syntax, and before you play with the pattern SQL, be sure to understand whether its behavior is consistent with the understanding.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.