Mysql SQL Select 最大值和平均值

发布于08月10日

环境为MySQL8.我有一张bird号桌

+----+--------+--------+
| id |  name  |  sex   |
+----+--------+--------+
| 1  | bob    | male   |
| 2  | mary   | female |
| 3  | jono   | male   |
+----+--------+--------+

鸟与Check联系在一起

+----+-------------+---------+
| id | check_date  | bird_id |
+----+-------------+---------+
| 1  | 2023-06-01  |       2 |
| 2  | 2023-07-01  |       2 |
| 3  | 2023-08-01  |       2 |
+----+-------------+---------+

每个check可以有几个weights个和measurements个:

weights个

+----+---------+----------+
| id | weight  | check_id | 
+----+---------+----------+
|  1 |    3.3  |        2 |
|  2 |    3.45 |        2 |
|  3 |    3.8  |        2 |
|  4 |    4.5  |        1 |
|  5 |    4.51 |        1 |
+----+---------+----------+

measurements个

+----+---------+----------+
| id | measur  | check_id |
+----+---------+----------+
|  1 |    95.2 |        1 |
|  2 |    97.4 |        1 |
|  3 |    96.4 |        1 |
|  4 |    99.1 |        3 |
|  5 |    98.1 |        3 |
+----+---------+----------+

对于给定的bird，我如何返回包含以下内容的结果集

+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
| bird.id | bird.name | newest check | date newest measurements | avg newest measurements | date newest weight | avg newest weight |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
|       2 | mary      | 2023-08-01   | 2023-08-01               |                    98.6 | 2023-07-01         |              3.52 |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+

我曾try 自己编写该查询，并取得了以下结果:

SELECT bird.id, bird.name, MAX(check.catch_date), weight
FROM bird
LEFT JOIN check ON bird.id=check.bird_id
LEFT JOIN measurements ON measurements.check_id=check.id
WHERE bird.id=2
GROUP BY id, weight;

But the way to nest and average the result of measurements个 and length which could be associated to different checks is where I am stuck so narrative on how the answer SQL works would be helpful.

select MAX(c.check_date) as date_newest_weight, ROUND(AVG(w.weight),2) as avg_newest_weight, c.bird_id from `check` c inner join weights w on c.id = (select max(w.check_id) from weights w where exists (select id from `check` c where c.bird_id = 2)) where c.bird_id = 2 group by c.bird_id;

select b.id as bird_id, b.name as bird_name, max(c.check_date) as newest_check, date_newest_measurements, avg_newest_measurements, date_newest_weight, avg_newest_weight from bird b inner join `check` c on b.id=c.bird_id inner join ( select MAX(c.check_date) as date_newest_measurements, ROUND(AVG(m.measur),2) as avg_newest_measurements, c.bird_id from `check` c inner join measurements m on c.id =(select max(m.check_id) from measurements m where exists (select id from `check` c where c.bird_id = 2)) where c.bird_id = 2 group by c.bird_id ) as max_meas on max_meas.bird_id=c.bird_id inner join ( select MAX(c.check_date) as date_newest_weight, ROUND(AVG(w.weight),2) as avg_newest_weight, c.bird_id from `check` c inner join weights w on c.id = (select max(w.check_id) from weights w where exists (select id from `check` c where c.bird_id = 2)) where c.bird_id = 2 group by c.bird_id ) as max_weig on max_weig.bird_id=c.bird_id group by bird_id, bird_name, date_newest_measurements, avg_newest_measurements, date_newest_weight, avg_newest_weight;

bird_id bird_name newest_check date_newest_measurements avg_newest_measurements date_newest_weight avg_newest_weight 2 mary 2023-08-01 2023-08-01 98.60 2023-07-01 3.52

Mysql SQL Select 最大值和平均值

推荐答案

Mysql相关问答推荐

如何在逗号分隔字符串值中使用LEFT函数

将值代入子查询

如何跨多个产品查找相同时间范围的数据集

返回包含某一列的分组最大值的行，说明列中的重复值

使用字符串文字与使用日期文字时的SQL行为

如何在 MySQL 中对同一个表的多个列进行 INNER JOIN

错误 2020 (HY000)：数据包大于 'max_allowed_packet' 字节，docker 容器内出现 mysql 错误

MySQL 计数出现的百分比大于

查找表中的唯一记录

Mysql，从两个不同的表中添加原始数据，从第一个表中获取所有内容，仅从第二个表中获取curdate内容

将 JSON 类型的列与特定字符串值进行比较

当每组的列值发生变化时 Select 并连接行

保存SQL查询的中间结果

MYSQL 或 Laravel Eloquent 如何从这个订单中统计细节

计算每个用户在第二个表中有多少条记录

将相同的固定值插入多行

如何在 MySQL 中 Select 字段具有最小值的数据？

仅在 MYSQL DATEDIFF 中显示小时数

MySQL 导出到 outfile：CSV 转义字符

当运行 UPDATE ... datetime = NOW();所有更新的行都会有相同的日期/时间吗？