mysql - 从mysql中的几个子查询中选择数据

Question

我的 mysql 有问题我需要从一些连接表中选择总和，但没有得到我需要的结果。这是我的查询。

SELECT kelas.kelas as kelas, perpus.perpus as perpus
    FROM
    (SELECT b.id_alt, SUM(a.nilai_crips) AS kelas FROM crips AS a JOIN list AS b ON a.id_crips=b.id_crips where a.id_kriteria=34 group by b.id_alt
    )as Kelas,
    (SELECT SUM(a.nilai_crips) AS perpus FROM crips AS a JOIN list AS b ON a.id_crips=b.id_crips where a.id_kriteria=35 group by b.id_alt
    )as perpus
    group by kelas.id_alt

第一列显示正确的结果，但第二列没有。

kelas | perpus
100   | 100
100   | 100
100   | 100
100   | 100
10    | 100
100   | 100
80    | 100
100   | 100

如果我删除了第一个子查询，该列将返回正确的结果。

perpus
100
100
100
100
100
100
100
76

我的查询有什么问题？预先感谢您的帮助。

Answer 1

使用单个 SELECT，并有条件地添加值:

SELECT 
    b.id_alt, 
    SUM(IF(a.id_kriteria = 34,a.nilai_crips,0)) AS kelas,
    SUM(IF(a.id_kriteria = 35,a.nilai_crips,0)) AS perpus
FROM crips AS a 
JOIN list AS b 
ON a.id_crips = b.id_crips 
WHERE a.id_kriteria IN (34,35)
GROUP BY b.id_alt;