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 The above grouping by a single field is very simple, and all records with the same 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 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:
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:
|
<<: How to Rename a Group of Files at Once on Linux
>>: Vue realizes picture switching effect
Recommend
CentOS 6.5 configuration ssh key-free login to execute pssh command explanation
1. Check and install pssh, yum list pssh 2. Becau...
Enable OCSP to improve https certificate verification efficiency and solve the problem of slow access to Let's Encrypt SSL certificates
In the past few days, the website has been access...
Introduction to the use of MySQL pt-slave-restart tool
Table of contents When setting up a MySQL master-...
The actual process of encapsulating axios in the project
Table of contents Preface Benefits of axios encap...
Detailed graphic tutorial on installing Ubuntu 20.04 dual system on Windows 10
win10 + Ubuntu 20.04 LTS dual system installation...
The process of installing Docker in Linux system
In this blog, I will walk you through the process...
Several ways to vertically and horizontally center in CSS3 with indefinite height and width
1. Flex layout .father { display: flex; justify-c...
Example of how to configure nginx to implement SSL
Environmental Description Server system: Ubuntu 1...
What is ZFS? Reasons to use ZFS and its features
History of ZFS The Z File System (ZFS) was develo...
The difference between KEY, PRIMARY KEY, UNIQUE KEY, and INDEX in MySQL
The problem raised in the title can be broken dow...
Tutorial on how to install and use Ceph distributed software under Linux
Table of contents Preface 1. Basic Environment 1....
How to install pyenv under Linux
Prerequisites Need to install git Installation St...
Solution to running out of MySQL's auto-increment ID (primary key)
There are many types of auto-increment IDs used i...
Solution to MySQL master-slave delay problem
Today we will look at why master-slave delay occu...
Step by step guide to build a calendar component with React
Table of contents Business Background Using Techn...