[Mysql] uses group by with having for cluster query and having Clustering

[Mysql] uses group by with having for cluster query and having Clustering

The target object of the cluster query is one of the columns in the table. For example, in the following testtable table, to find out the number of occurrences of each item in the username column, cluster query is used.

Obviously, after clustering query, the result must have no relationship with the half-point of the id and number columns. Therefore, as mentioned at the door, clustering query targets a column in a table.

Clustering queries are often used in combination with clustering statements such as count (*), sum (*), and avg (*) for table statistics. Because the queried fields are often of the varchar type, the count (*) function is the most frequently used to count the number of strings.

For example, to query the above testtable table, to find out the number of times each item appears in the username column, the SQL statement is as follows:

SELECT username,count(*) as mount FROM testtable group by username order by mount desc

The query result is as follows:

Here, order by is used to sort the query results in descending order.

After comparing the query results, we can find that group by means clustering the same items into a row. Use the count function to count the number of occurrences and separate them into one column.

So what if I don't want to show or do not want to count the results with less than 1? Now you should attach a having statement to the group by statement.

For example, the following statement is used to query the items with the username line appearing more than 2 times:

SELECT username,count(*) as mount FROM testtable group by username having mount>1 order by mount desc

Having is not a parallel relationship with group by. having is a subsidiary Statement of group by, and must have group by and having.

Using group by with having for cluster query is often accompanied by [Mysql] Using Inner join and nested query to implement multi-Table query, basic concepts of primary key and foreign key (click to open the link) multi-Table query to find more in-depth results.