mysql - 当应该显示多行时，具有嵌套选择的 SQL 仅返回一行

Question

我有以下sql:

SELECT          
            ROUND(SUM(MS.score) / count(P.module_id),2) AS average_score,
            (SELECT Count(MS.phase_id) FROM Module_score MS WHERE MS.phase_id = P.id) as attempts,
            (SELECT ROUND(Count(MS.phase_id)/ (SELECT COUNT(user_id) FROM User_has_Team WHERE Team_id = 1) *100,2) FROM Module_score MS WHERE phase_id = P.id) as percent_completed
            FROM
              Module_score MS
                 LEFT JOIN
            `Phase` P ON P.module_id = MS.module_id
                JOIN User U on U.id = MS.user_id
            WHERE
             P.academy_id = 1 AND P.team_id = 1 AND P.start < now()

这将返回以下内容:

# id, average_score, attempts, percent_completed
 '89',   '3.94',         '2',      '100.00'

现在这很好，但是它应该返回 now() 之前的所有阶段

如下:

相表

'89', '1', '1', '1', '2014-07-08 00:00:00', '1', 'none', '22:22', 'none', '1'
'90', '1', '1', '1', '2014-07-22 00:00:00', '2', 'none', 'none', 'none', '1'
'91', '3', '1', '1', '2014-07-08 00:00:00', '3', 'gege', '123451', 'Marc Rasmussen', '0'
'92', '1', '1', '1', '2013-02-15 00:00:00', '4', 'none', 'none', 'none', '1'

我尝试了很多不同的方法，但没有运气

任何人都可以将我推向正确的方向或向我解释为什么会发生这种情况吗？

Answer 1

这是您的查询:

SELECT ROUND(SUM(MS.score) / count(P.module_id),2) AS average_score,
-------------^ --------------^
       (SELECT Count(MS.phase_id) FROM Module_score MS WHERE MS.phase_id = P.id) as attempts,
       (SELECT ROUND(Count(MS.phase_id)/ (SELECT COUNT(user_id) FROM User_has_Team WHERE Team_id = 1) *100,2) FROM Module_score MS WHERE phase_id = P.id) as percent_completed
FROM Module_score MS LEFT JOIN
     `Phase` P
     ON P.module_id = MS.module_id JOIN
     User U on U.id = MS.user_id
WHERE P.academy_id = 1 AND P.team_id = 1 AND P.start < now();

这些将查询转变为聚合查询。如果没有 group by，此类查询将仅返回一行。在大多数版本的 SQL 中，此查询将返回错误，因为您使用 p.id 关联子查询，但这不是 group by 子句的一部分。 MySQL 通过group by 扩展允许这样做。我猜你想要:

SELECT p.id,
       ROUND(SUM(MS.score) / count(P.module_id),2) AS average_score,
       (SELECT Count(MS.phase_id) FROM Module_score MS WHERE MS.phase_id = P.id) as attempts,
       (SELECT ROUND(Count(MS.phase_id)/ (SELECT COUNT(user_id) FROM User_has_Team WHERE Team_id = 1) *100,2) FROM Module_score MS WHERE phase_id = P.id) as percent_completed
FROM Module_score MS LEFT JOIN
     `Phase` P
     ON P.module_id = MS.module_id JOIN
     User U on U.id = MS.user_id
WHERE P.academy_id = 1 AND P.team_id = 1 AND P.start < now()
GROUP BY p.id;

这可能会达到你想要的效果。相关子查询看起来相当复杂，所以我怀疑它们可以简化。但是，尚不清楚您真正想要什么结果。