当前位置:网站首页>When MySQL uses GROUP BY to group the query, the SELECT query field contains non-grouping fields
When MySQL uses GROUP BY to group the query, the SELECT query field contains non-grouping fields
2022-08-11 07:27:00 【zero round】
When writing SQL today, I found that when GROUP BY is used to group query, the SELECT query field contains non-grouping fields, which will cause the field information to not match. Write this record and introduce a solution.
Description of the problem:
In Nioke SQL33 to find out the students with the lowest GPA in each school, this question has the user_profile table with the following fields and data:
It is required to find the students with the lowest gpa in each school and output in the following way:
Personal thoughts (mistakes)
The first thing that comes to my mind is to use group by to group the data according to the school, and then use the min function to get the students with the lowest gpa in each school, that is, the following code:
SELECT `device_id`,`university`,MIN(`gpa`)FROM `user_profile`GROUP BY `university`ORDER BY `university` ASC;
But I found that it will generate errors at runtime. After the query, it is found that the school and the student have a one-to-many relationship. When the field in the select is neither a grouping field nor an aggregation function, use this queryThe method will aggregate all the information of the same field of multiple students into one piece, and this piece of information will be randomly selected from all the information, as shown below:
This device_id and gpa are the data of each school, randomly select a record of a classmate as the data, and then use the min function to find the classmate with the smallest gpa in each school, although we can get the smallest gpa, but the student with the smallest gpaThe device_id and the device_id in the school after the party may not correspond (here it just queries the minimum gpa of each school according to the school grouping, and uses the minimum gpa as the gpa field in the grouping table, but the device_id in the grouping table isIt will not be replaced according to the minimum gpa, it is still the original device_id), you can see the difference from the following figure:
We can see that although the minimum gpa of Peking University is obtained, the device_id does not correspond.This is because in the case of one-to-many, the selected field uses a non-grouping field (non-aggregation function) to aggregate the data of the field, and only one random field value is retained at the end, resulting in data mismatch.
Solution:
In order to solve this problem, we usually have a way to solve it:
1. Subquery
First find the minimum gpa of each school through a subquery. This subquery only contains the university and MIN(gpa) fields, because the two are grouping fields and aggregation functions, and will not cause data aggregation, so you can get normal resultsthe minimum gpa corresponding to each school in theThe minimum gpa field is now (this can also be achieved through a sliding window)
SELECT a.device_id,a.university,a.gpaFROM user_profile aJOIN (SELECT university,min(gpa) gpaFROM user_profileGROUP BY university) bon a.university=b.university and a.gpa=b.gpaORDER BY university;
But what if there are multiple same minimum gpa in the same school, I have not solved this problem.
Summary:
When using grouping query, the field after select is either a grouping field or an aggregation function
边栏推荐
- HCIP experiments (pap, chap, HDLC, MGRE, RIP)
- Discourse 的关闭主题(Close Topic )和重新开放主题
- JD.com product details API call example explanation
- 概念名词解释
- Pinduoduo API interface (attach my available API)
- 每日sql-统计各个专业人数(包括专业人数为0的)
- 《Show and Tell: A Neural Image Caption Generator》论文解读
- 姿态解算-陀螺仪+欧拉法
- HCIP MGRE\OSPF Comprehensive Experiment
- Resolved EROR 1064 (42000): You have an error in. your SOL syntax. check the manual that corresponds to yo
猜你喜欢
随机推荐
第一个C函数:如何实现板级初始化?
pytorch,numpy两种方法实现nms类间+类内
基于FPGA的FIR滤波器的实现(5)— 并行结构FIR滤波器的FPGA代码实现
Spatial Pyramid Pooling -Spatial Pyramid Pooling (including source code)
拼多多API接口(附上我的可用API)
拼多多api接口应用示例
Taobao sku API interface (PHP example)
损失函数——负对数似然
radix-4 FFT principle and C language code implementation
联想集团:2022/23财年第一季度业绩
Redis测试
Daily sql-seek the sum of successful investments in 2016
Class definition, class inheritance, and the use of super
Multiscale communication in cortical-cortical networks
MySQL之CRUD
sql--Users who have purchased more than 3 times (inclusive) within 7 days (including the current day), and the purchase amount in the past 7 days exceeds 1,000
技能在赛题解析:交换机防环路设置
【预约观看】Ambire 智能钱包 AMA 活动第四期即将举行
OA project meeting notice (query & whether attending & feedback for details)
矩阵分析——微分、积分、极限