MySQL group by method for single word grouping sequence and multi-field grouping

MySQL group by method for single word grouping sequence and multi-field grouping

I have created a goods table here. Let's take a look at the data in it:

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. Group by 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,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 to display detailed information about the group.

The above grouping by a single field is very simple, and all records with the same s_id are grouped together.

2. Group by s_id, goods_desc fields

Analysis: When querying groups here, the data will be grouped according to s_id first, 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 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 |
+----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+

A comparison between goods_descs here and above will make it clear

Next, you can 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 |
+----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+

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

In daily development tasks, we often use MYSQL's GROUP BY grouping to obtain statistical data based on the grouping fields in the data table. For example, there is a student course selection table with the following structure:

Table: Subject_Selection

Subject Semester Attendee
---------------------------------
ITB001 1 John
ITB001 1 Bob
ITB001 1 Mickey
ITB001 2 Jenny
ITB001 2 James
MKB114 1 John
MKB114 1 Erica

We want to count how many students have signed up for each course, and apply the following SQL:

SELECT Subject, Count(*)
FROM Subject_Selection
GROUP BY Subject

The following results are obtained:

Subject Count
------------------------------
ITB001 5
MKB114 2

Because the table records that 5 students chose ITB001 and 2 students chose MKB114.

The reasons for this result are:

GROUP BY X means putting all records with the same X field value into one group.

What about GROUP BY X, Y?

GROUP BY X, Y means putting all records with the same X and Y field values ​​into one group.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:
  • Detailed explanation of whether the MySQL database should use foreign key constraints
  • Why is the disk space still occupied after deleting table data in MySQL?
  • In-depth analysis of MySQL index data structure
  • MySQL essential basics: grouping function, aggregate function, grouping query detailed explanation
  • MySQL query statement grouped by time
  • Detailed explanation of the group by statement in MySQL database group query
  • Example of implementing grouping and deduplication in MySQL table join query
  • MySQL group query optimization method
  • MySQL data aggregation and grouping

<<:  How to Rename a Group of Files at Once on Linux

>>:  Vue realizes picture switching effect

Recommend

Detailed explanation of MySQL startup options and system variables examples

Table of contents Boot Options Command Line Long ...

CSS to achieve the like card effect in the lower right corner of the web page (example code)

Effect To implement HTML, first prepare a clean H...

How to delete folders, files, and decompress commands on Linux servers

1. Delete folders Example: rm -rf /usr/java The /...

Detailed explanation of Vue project packaging

Table of contents 1. Related configuration Case 1...

Installation and uninstallation of MySQL 5.7 decompressed version and summary of common problems

1. Installation 1. Download Go to the MySQL offic...

MySQL learning to create and operate databases and table DDL for beginners

Table of contents 1. Operate the database 1.1 Cre...

Vue project realizes paging effect

The paging effect is implemented in the vue proje...

JavaScript to achieve a simple page countdown

This article example shares the specific code of ...

MySQL batch adding and storing method examples

When logging in to the stress test, many differen...

A complete list of commonly used HTML tags and their characteristics

First of all, you need to know some characteristi...

Detailed tutorial for installing MySQL on Linux

MySQL downloads for all platforms are available a...

uniapp dynamic modification of element node style detailed explanation

Table of contents 1. Modify by binding the style ...

Solution to the error when installing Docker on CentOS version

1. Version Information # cat /etc/system-release ...

Introduction to new features of ECMAscript

Table of contents 1. Default values ​​for functio...

Mysql WorkBench installation and configuration graphic tutorial

This article shares with you the installation and...