我有这个SQL查询(我使用的是MySQL 8.0.35):
SELECT MAX(su) as value, activity_id, user_id, u.faculty_id AS faculty_id
FROM (
SELECT SUM(s.distance) as su, s.activity_id as activity_id, s.user_id as user_id, s.date
FROM submission s
WHERE s.week = 2 AND s.accepted = 1 AND s.season_id = 859
GROUP BY s.date, s.user_id, s.activity_id
) as sums
INNER JOIN user u ON user_id = u.id
GROUP BY activity_id;
它所做的是:计算最大距离和,并获取"其他"相关列. 在结果中,可以有多行(理想情况下每个活动一行).
但是,它为"Factoryid"列返回了不正确的值.
我try 使用几个不同的WHERE子句将USER表上的INTERN JOIN放在嵌套的SELECT中,但似乎没有返回正确的结果.
部分用户表:
有了这些数据,我预计结果将是:
value | activity_id | user_id | faculty_id |
---|---|---|---|
456 | 743 | 265 | 3375 |
456 | 744 | 265 | 3375 |
但是,我得到的结果是(请注意anteach_id):
value | activity_id | user_id | faculty_id |
---|---|---|---|
456 | 743 | 265 | 3373 |
456 | 744 | 265 | 3375 |