51工具盒子英文:
SQL Select Max and Average
问题 {#heading}
以下是您请求的部分翻译：
环境是MySQL8。我有一个名为`bird`的表格。
鸟与`Check`相关联
每个`check`可以有多个`weights`和`measurements`：
`weights`
`measurements`
对于给定的`bird`，我应该如何返回一个包含以下结果集：
我尝试自己编写查询并且做到了：
但如何嵌套和平均`measurements`和`length`的结果，它们可能与不同的`checks`相关联，这是我卡住的地方，因此关于答案SQL如何工作的解释将会有所帮助。
请注意，我只翻译了您提供的代码和问题的相关部分。如果您需要进一步的帮助或解释，请随时提出。 英文:
Environment is MySQL8. I have a table bird
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | bob | male |
| 2 | mary | female |
| 3 | jono | male |
+----+--------+--------+
Bird is associated to Check
+----+-------------+---------+
| id | check_date | bird_id |
+----+-------------+---------+
| 1 | 2023-06-01 | 2 |
| 2 | 2023-07-01 | 2 |
| 3 | 2023-08-01 | 2 |
+----+-------------+---------+
And each check can have several weights and 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 |
+----+---------+----------+
For a given bird how would I return a result set containing
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
| 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 |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
I have attempted to write the query myself and got as far as :
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.
答案1 {#1}
得分: 2
我建议不要使用 Keywords and Reserved Words ，比如 check ，因为你必须始终使用反引号。
关于这个问题（也许有一个简单的解决方案）。
但是嵌套和平均测量结果和长度，可能与不同的检查相关联，这是我卡住的地方。
可以通过连接和使用存在来检索最大的 check_id 来实现。
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;
结果
date_newest_weight avg_newest_weight bird_id
2023-07-01 3.91 2
最终查询
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
在这里可以看到示例。 英文:
I recommend not using Keywords and Reserved Words such as check because you have to always use backticks.
Regarding the question (maybe there is a simple solution).
> 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
This can be done by join and the help of exist to retrieve the max check_id
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;
Result
date_newest_weight avg_newest_weight bird_id
2023-07-01 3.91 2
Final query
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;
Result
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
See example here
