MySQL self-Summary

Group by and distinct

Group by group, each group displays only one record

Distinct is followed by multiple fields to ensure that each field combination record is unique (distinct ID, name from)

Groupby having and where

Select *, (sum (WIN)/count (*) as present from 'xxtable' group by heroHaving present> 0.1

Select *, (sum (WIN)/count (*) as present from 'xxtable' where present> 0.1 group by hero (statement error, cannot find present)

The where clause is usedBefore grouping query results, remove rows that do not meet the where condition, that is, filter data before grouping. The condition cannot contain a clustering function and the where condition is used to display specific rows.

HavingUsed in aggregate functions to determine whether the value of Aggregate functions (sum, Count, AVG,) is greater than a certain value

Instance training:

1. League of legends multi-player Magic Box

Table

A: gets the top 2 heroes and matches

Select *, (sum (WIN)/count (*) as present from 'A' where userid = 1 group by hero order by present DESC limit 2

B: Obtain heroes and matches with a winning rate greater than 0.5

Select *, (sum (WIN)/count (*) as present from 'A' where userid = 1 group by hero having present> 0.5 order by present DESC