mysql - 需要有关 MYSQL QUERY (SUM) 的帮助

Question

我需要你帮助我做一个 SELECT QUERY。我只是想正确总结下表。该表是其他几个表的综合。我可能会为学生姓名而不是文本添加一个 id int 值，以便更容易组合 - 但这不是我的问题。

我想为每个学生总结三种缺课类型。但是对于我的sql，我每次只得到一个!我究竟做错了什么？我尝试了几个更改，但我没有找到。

我想从我的 mysql 表中添加一个屏幕，但我没有足够的声誉..

SELECT DISTINCT ( P.synthese_abs_schueler_name ) AS synthese_abs_schueler_name, 
                SUM(A.synthese_sum)              AS absent, 
                SUM(B.synthese_sum)              AS nonexcused, 
                SUM(C.synthese_sum)              AS tardive, 
                SUM(absent + nonexcused)         AS totalabs, 
FROM   synthese_abs AS P 
       LEFT JOIN (SELECT synthese_abs_schueler_name, 
                         synthese_abs_type, 
                         synthese_abs_sum AS synthese_sum 
                  FROM   synthese_abs 
                  WHERE  synthese_abs_type = 1 
                         AND synthese_abs_sum > 0 
                  GROUP  BY synthese_abs_schueler_name) AS A 
              ON A.synthese_abs_type = P.synthese_abs_type 
                 AND A.synthese_abs_schueler_name = P.synthese_abs_schueler_name 
       LEFT JOIN (SELECT synthese_abs_schueler_name, 
                         synthese_abs_type, 
                         synthese_abs_sum AS synthese_sum 
                  FROM   synthese_abs 
                  WHERE  synthese_abs_type = 2 
                         AND synthese_abs_sum > 0 
                  GROUP  BY synthese_abs_schueler_name) AS B 
              ON B.synthese_abs_type = P.synthese_abs_type 
                 AND B.synthese_abs_schueler_name = P.synthese_abs_schueler_name 
       LEFT JOIN (SELECT synthese_abs_schueler_name, 
                         synthese_abs_type, 
                         synthese_abs_sum AS synthese_sum 
                  FROM   synthese_abs 
                  WHERE  synthese_abs_type = 3 
                         AND synthese_abs_sum > 0 
                  GROUP  BY synthese_abs_schueler_name) AS C 
              ON C.synthese_abs_type = P.synthese_abs_type 
                 AND C.synthese_abs_schueler_name = P.synthese_abs_schueler_name 
ORDER  BY P.synthese_abs_schueler_name 

Answer 1

您可以如下重写您的查询，而不是使用许多自连接，您可以在 SUM() 中使用 CASE 来求和 synthese_sum关于 synthese_abs_type 的值以及缺席和非借口的总和，我使用了子选择，因为在同一级别的查询中，您可以在选择中使用聚合结果的别名，要么使用子选择，要么重复整个表达式

SELECT *, absent + nonexcused  AS totalabs
FROM (
SELECT  synthese_abs_schueler_name ,
    SUM(CASE WHEN synthese_abs_type = 1 THEN synthese_sum ELSE 0 END) AS absent,
    SUM(CASE WHEN synthese_abs_type = 2 THEN synthese_sum ELSE 0 END) AS nonexcused,
    SUM(CASE WHEN synthese_abs_type = 3 THEN synthese_sum ELSE 0 END)  AS tardive
FROM 
synthese_abs 
WHERE synthese_abs_sum >0 
GROUP BY synthese_abs_schueler_name 
ORDER BY synthese_abs_schueler_name 
) t