How does the MySQL GROUP BY clause behave like a DISTINCT clause?-Mysql Tutorial-php.cn

How does the MySQL GROUP BY clause behave like a DISTINCT clause?

WBOY

Release： 2023-09-08 13:09:02

forward

1095 people have browsed it

MySQL GROUP BY 子句如何表现得像 DISTINCT 子句？

When we use GROUP BY clause in SELECT statement without using aggregate function, it behaves like DISTINCT clause. For example, we have the following table -

mysql> Select * from testing;
+------+---------+---------+
| id   | fname   | Lname   |
+------+---------+---------+
|  200 | Raman   | Kumar   |
|  201 | Sahil   | Bhalla  |
|  202 | Gaurav  | NULL    |
|  203 | Aarav   | NULL    |
|  204 | Harshit | Khurana |
|  205 | Rahul   | NULL    |
|  206 | Piyush  | Kohli   |
|  207 | Lovkesh | NULL    |
|  208 | Gaurav  | Kumar   |
|  209 | Raman  | Kumar    |
+------+---------+---------+
10 rows in set (0.00 sec)

Copy after login

By using the DISTINCT clause on the "Lname" column, MySQL returns the following result set.

mysql> select Distinct LNAME from testing;
+---------+
| LNAME   |
+---------+
| Kumar   |
| Bhalla  |
| NULL    |
| Khurana |
| Kohli   |
+---------+
5 rows in set (0.00 sec)

Copy after login

Now, by using the GROUP BY clause as shown below, we can get the same result set as that obtained using DISTINCT -

mysql> Select LNAME from testing GROUP BY Lname;
+---------+
| LNAME   |
+---------+
| NULL    |
| Bhalla  |
| Khurana |
| Kohli   |
| Kumar   |
+---------+
5 rows in set (0.04 sec)

Copy after login

We can observe the results returned by MySQL There is a difference between the sets, a MySQL query using the GROUP BY clause returns a result set that is sorted, while a MySQL query using the DISTINCT clause returns an unsorted result set. p>

The above is the detailed content of How does the MySQL GROUP BY clause behave like a DISTINCT clause?. For more information, please follow other related articles on the PHP Chinese website!