MySQL：按另一个表中的行数排序？

Question

SELECT e.*,
(
    SELECT COUNT(*) FROM
    (
        SELECT tm.exerciseID FROM exercise_targetedmuscles tm
        WHERE tm.exerciseID = e.exerciseID
        AND tm.isPrimary = 1
        AND tm.targetedMuscleID IN (4,11)
    ) as musclesCovered
) as numMusclesCovered
FROM exercises e
ORDER BY numMusclesCovered DESC

基本上，我想按照练习所覆盖的目标肌肉数量对练习进行排序（在示例中，目标肌肉是 4 块和 11 块）。但是，子查询（出于某种原因？）不知道 e 是什么，所以它不起作用。

关于如何让这个查询正确排序我的结果有什么想法吗？谢谢！

编辑：在兰迪的有用评论后想出了这个。

SELECT COUNT(tm.targetedMuscleID) as numMusclesCovered, e.*, tm.* FROM exercise_targetedmuscles tm

JOIN exercises e ON tm.exerciseID = e.exerciseID

WHERE tm.isPrimary = 1
AND tm.targetedMuscleID IN (4,11)

GROUP BY tm.exerciseID

ORDER BY numMusclesCovered DESC

非常感谢！

Answer 1

了解表格的结构可能会有所帮助。我建议一个与此类似的解决方案。

SELECT e.*, tm.*, COUNT(tm.targetedMuscleID) AS coveredMuscles
FROM exercises AS e
JOIN exercise_targetedmuscles AS tm ON tm.exerciseID = e.exerciseID
WHERE tm.isPrimary = 1 AND tm.targetedMuscleID IN (4, 11)
GROUP BY e.exerciseID
ORDER BY coveredMuscles;

Answer 2

如果我理解正确的话，你根本不需要子查询

SELECT count(tm.targetedMuscleID) as num_muscles, e.exerciseID 
FROM exercise_targetedmuscles tm, exercises e
WHERE tm.exerciseID = e.exerciseID
AND tm.isPrimary = 1
AND tm.targetedMuscleID IN (4,11)
GROUP BY e.exerciseID
order by num_muscles

Answer 3

MySQL 中的 SQL 子查询有类似“本地命名空间”的东西。我没有能力进行测试，但您可能正在搜索类似这样的内容：

SELECT `data`.* FROM (
   SELECT COUNT(tm.targetedMuscleID) as `count`,tm.targetedMuscleID FROM exercise_targetedmuscles tm
   LEFT JOIN `exercises` as `e` ON (`tm`.`exerciseID`=`e`.`exerciseID`)
   WHERE
       tm.isPrimary = 1
   AND
       tm.targetedMuscleID IN (4,11)
   GROUP BY tm.targetedMuscleID
) as `data`
ORDER BY `data`.`count`

但是，在某些情况下，您根本不必使用子查询。