mysql - 如何连接两个 SELECT 语句？

Question

SELECT fullname,
SUM(IF(mark='c',1,0)) AS compensations,
ROUND(AVG(mark)) AS avg_mark
FROM evaluation
WHERE subject='$subject' AND fullname IN
(SELECT fullname
FROM campus
WHERE user_levels=1)
GROUP BY fullname ASC


SELECT fullname,
SUM(IF(attend='a',1,0)) AS absences
FROM attendance 
WHERE subject='subject I' AND fullname IN
(SELECT fullname FROM campus WHERE user_levels=1) 
GROUP BY fullname ASC

我需要显示与以下语句匹配的每个学生:全名、平均分数、报酬和平均值。

这两个代码单独运行效果很好，但我似乎无法将它们放在一起。请提供任何帮助，我们将不胜感激。

Answer 1

那些表看起来确实很奇怪。为什么要将全名存储在 3 个不同的表中？看起来不太对劲。

另外:

• 优先使用 INNER JOIN 而不是 IN ( ... )
• 建议在 IF() 函数上使用 case 表达式

但是，我认为以下方法可能有效:

SELECT
      c.fullname
    , SUM(CASE
            WHEN e.mark = 'c' THEN 1
            ELSE 0
      END) AS compensations
    , ROUND(AVG(e.mark)) AS avg_mark
    , SUM(CASE
            WHEN a.attend = 'a' THEN 1
            ELSE 0
      END) AS absences
FROM campus c
LEFT JOIN evaluation e
      ON c.fullname = e.fullname
      AND e.subject = '$subject'
LEFT JOIN attendance a
      ON c.fullname = a.fullname
      AND a.subject = 'subject I'
WHERE c.user_levels = 1
GROUP BY c.fullname
;