Shulou(Shulou.com)06/01 Report--

I have created a goods table here. Take a look at the data in it first:

Mysql > select * from goods +-- +-+ | id | s_id | b_id | goods_name | goods_price | goods_desc | + | 1 | 1 | 5 | book | 22.35 | book | | 2 | 2 | 5 | ball | 32.25 | ball | 3 | 3 | 5 | NULL | 3.23 | NULL | 4 | 3 | 5 | macbook | 3.23 | book | 5 | 3 | 5 | listbook | 2.30 | book | 6 | 1 | 1 | nicebook | 9999.00 | nicebook | | 7 | | | 2 | 3 | googlebook | 25.30 | book | +-+ |

1. Grouping according to s_id

Mysql > select *, group_concat (goods_name) goods_names,group_concat (goods_desc) goods_descs,group_concat (id) ids,group_concat (goods_price) goods_prices from goods group by s_id +-+ | Id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices | +- -+ | 1 | 1 | 5 | book | 22.35 | book | book Nicebook | book,nicebook | 1 NULL 6 | 22.35 Magi 9999.00 | 2 | 2 | 5 | ball | 32.25 | ball | ball,googlebook | ball,book | 2 Magi 7 | 32 25 Magi 25.30 | | 3 | 3 | 5 | NULL | 3.23 | NULL | macbook,listbook | book,book | 3 Magi 4 5 | 3.23 ~ 3.23 ~ 2.30 | +-+

The group_concat () function is used here to display the details of the grouping

The above grouping according to a single field is very simple, grouping all the records of the same s_id

2. Group according to the field of squarid.goodsroomdesc.

Analysis: when querying grouping here, it will first be grouped according to s_id, and then the data in each group will be grouped according to goods_desc

Mysql > select *, group_concat (goods_name) goods_names,group_concat (goods_desc) goods_descs,group_concat (id) ids,group_concat (goods_price) goods_prices from goods group by +-+ | id | s_id | | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices | +- -+ | 1 | 1 | 5 | book | 22.35 | book | 1 | 6 | 1 | nicebook | 9999.00 | nicebook | 6 | 9999.00 | | 2 | 2 | 5 | ball | 2 | 32.25 | | 7 | 2 | 3 | googlebook | 25.30 | book | googlebook | book | 7 | 25.30 | 3 | 3 | 5 | NULL | 3.23 | NULL | 3 | 3.23 | 4 | 5 | macbook | 3.23 | book | macbook Listbook | book,book | 4Piaget 5 | 3.23 Magi 2.30 | +-+

The goods_descs here is clear by comparison with the one above.

Next, you can also group according to goods_price.

Mysql > select *, group_concat (goods_name) goods_names,group_concat (goods_desc) goods_descs,group_concat (id) ids,group_concat (goods_price) goods_prices from goods group by +-- + | id | s_id | b_id | Goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices | +-- + -+ | 1 | 1 | 5 | book | 1 | 22.35 | 6 | 1 | nicebook | 9999.00 | nicebook | 6 | 9999.00 | 2 | 2 | ball | 2 | 32.25 | 7 | 2 | 3 | googlebook | book | googlebook | book | | 7 | 25.30 | 3 | 3 | 5 | NULL | 3.23 | NULL | NULL | 3 | 3.23 | 5 | 3 | 5 | listbook | 2.30 | book | listbook | book | 5 | 2.30 | 4 | 3 | 5 | macbook | 3.23 | book | macbook | book | 4 | 3.23 | + -+

Here, when grouping multiple fields, you only need to know that the fields behind the grouping order are grouped according to the contents of the previous fields.

In the usual development task, we often use the GROUP BY grouping of MYSQL to obtain the statistics based on the grouping field in the data table. For example, there is a student course selection table, which is structured as follows:

Table: Subject_Selection

Subject Semester Attendee--ITB001 1 JohnITB001 1 BobITB001 1 MickeyITB001 2 JennyITB001 2 JamesMKB114 1 JohnMKB114 1 Erica

We would like to count the number of students who have signed up for each course, using the following SQL:

SELECT Subject, Count (*) FROM Subject_SelectionGROUP BY Subject

The results are as follows:

Subject Count

-

ITB001 5

MKB114 2

Because the table records that there are 5 students who choose ITB001,2 and students who choose MKB114.

The reasons for this result are:

GROUP BY X means to put all records with the same X field value into a group.

What about GROUP BY X, Y?

GROUP BY X, Y means to put all records with the same X field value and Y field value into a group.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.