MySQL GROUP BY single-word grouping and multi-field grouping

I created a goods table here, first look at the data inside:

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, according to S_ID Group

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,6   |  22.35,9999.00  | |   2 |    2 |    5 | ball        |       32.25 | ball        | ball,googlebook  | ball,book      | 2,7   | 32.25,25.30    | |   3 |    3 |    5 | NULL        |        3.23 | NULL        | macbook,listbook | book,book      | 3,4,5 | 3.23,3.23,2.30 |+----+------+------+------------+-------------+----------- -+------------------+---------------+-------+----------------+ 

The Group_concat () function is used here primarily to show the details of the grouping

The above based on a single field group is simple, the same s_id records are grouped

2. Group BY S_id,goods_desc Field

Analysis: When the query is grouped, it is grouped according to s_id, then the data inside each group is then 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 s_id,goods_desc;+----+------+------+------------+-------------+------------+--------------- ---+-------------+------+--------------+| 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             | book         | 1    | 22.35        | |   6 |    1 |    1 | nicebook    |     9999.00 | nicebook   | nicebook          | nicebook    | 6     | 9999.00      | |   2 |    2 |    5 | ball        |       32.25 | ball        | ball              | ball        | 2     | 32.25        | |   7 |    2 |    3 | googlebook |        25.30 | book       |  googlebook       | book         | 7    | 25.30        | |   3 |    3 |    5 | NULL        |        3.23 | NULL        | NULL              | null        | 3    |  3.23         ||  4 |    3 |    5 | macbook     |        3.23 | book        | macbook,listbook | book,book   | 4,5  |  3.23,2.30    |+----+------+------+------------+-------------+------------+---------------- --+-------------+------+--------------+

The Goods_descs here and the comparison above are clear.

Next, you can also group by 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 s_id,goods_desc,goods_price;+----+------+------+------------+-------------+------------+--- ----------+-------------+------+--------------+| 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         | book        | 1    | 22.35         | |   6 |    1 |    1 | nicebook    |     9999.00 | nicebook   | nicebook     | nicebook    | 6    | 9999.00       | |   2 |    2 |    5 | ball        |       32.25 | ball        | ball        | ball         | 2    | 32.25         | |   7 |    2 |    3 | googlebook |        25.30 | book       | googlebook  | book         | 7    | 25.30         | |   3 |    3 |    5 | NULL        |        3.23 | NULL        | 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         | +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+

Summary: Here is mainly to do multiple field grouping, only need to grasp the grouping order after the field is grouped according to the previous field after the grouping.

This article is from the "Slag 2nd" blog, please be sure to keep this source http://keepstrive.blog.51cto.com/10004407/1621641

MySQL GROUP BY single-word grouping and multi-field grouping