Tips for group query by group

Stars · Posted on 8/4/2017 1:04:14 PM

1.Group by is used alone, and the query results show only one record in a group.
SELECT * FROM `employee`; Show results

select * from employee group by sex; Show results

Only two records will be displayed, this value is obviously incorrect, no collection function is used, and the value displayed is only the first data for each group.
If you want to group and display data, consider using itgroup_concat() function,

2.Used with the group_concat() function,The specified field values in each grouping are displayed
Come on
select sex,group_concat(name) from employee group by sex;

Then use the explode and implode functions to extract the data.
3.Used with set functions,Set functions include count(), sum(), avg(), max(), and min().
select *,count(sex) as total from employee group by sex;

4 Used with having
"Having conditional expressions", which can limit the output result. Only the result of the conditional expression is displayed.
Instance:

[color=white !important]

?

select sex,count(sex) from employee group by sex having count(sex) >= 3;

Outcome:

[color=white !important]

?

1 2	sex \| count（sex Male \| 3

havingconditional expression" acts on the grouped records.
5.Group by multiple fields
select * from employee from group by sex,score;
The query results are grouped by sex first, and then by score
6 withUse with rollup
Using with rollup will add a record to the end of all records, which is the sum of all the records above

1	select sex,count(sex) from employee group by sex with rollup;

Outcome:

[color=white !important]

?

1

2

3

4

sex | count（sex）
Female | 1
Male | 5
null | 6

If it is a string, for example, the name will generate a result of the type "Zhang San, Li Si, Wang Wu", that is, the sum of names.

Stars · Posted on 8/4/2017 1:10:17 PM

Similarities and differences between having and where

                  Having is similar to where, you can filter the data, how to write the expression after where, and how to write it after having
                  where queries the data for columns in the table
                  Having works on columns in query results, filtering data
                  #查询本店商品价格比市场价低多少钱, output goods with a lower price of more than 200 yuan
                  select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;
                  You can't use where because s is the query result, and where can only filter for field names in the table
                  If you use where, it is:
                  select goods_id,goods_name from goods where market_price - shop_price > 200;

                  #同时使用where与having
                  select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;
                  #查询积压货款超过2万元的栏目, as well as the backlog of payments in this column
                  select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s > 20000
                  #查询两门及两门以上科目不及格的学生的平均分
                        Idea:
                        #先计算所有学生的平均分
                           select name,avg(score) as pj from stu group by name;
                        #查出所有学生的挂科情况
                        select name,score<60 from stu;
                                 #这里score<60 is a judgment statement, so the result is true or false, and the true is 1 in mysql and 0 is false
                        #查出两门及两门以上不及格的学生
                        select name,sum(score<60) as gk from stu group by name having gk > 1;
                        #综合结果
                        select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;

Little scum · Posted on 8/4/2017 5:47:19 PM

This can be inserted into the code

QWERTYU · Posted on 8/7/2017 1:47:02 PM

Landlord group by post-check* No error?

[Tips] Tips for group query by group

Score