如何使用“Case When”和“Group By”创建 Mysql 查询

debugcn 发表于 Dev

伊尔哈斯

给定我的数据库中的 3 个表（prodi,mahasiswa和status），我试图输出分组和计数的结果。

这张图片中的数据库

询问

SELECT
prodi.namaprodi,
(case when count(`status`.idsm)='1' then 1 else 0 end) as '1',
(case when count(`status`.idsm)='2' then 1 else 0 end) as '2',
(case when count(`status`.idsm)='3' then 1 else 0 end) as '3',
(case when count(`status`.idsm)='4' then 1 else 0 end) as '4',
(case when count(`status`.idsm)='5' then 1 else 0 end) as '5',
(case when count(`status`.idsm)='6' then 1 else 0 end) as '6',
(case when count(`status`.idsm)='7' then 1 else 0 end) as '7',
(case when count(`status`.idsm)='8' then 1 else 0 end) as '8'
FROM
`status`
INNER JOIN mahasiswa ON mahasiswa.idm = `status`.idm
INNER JOIN prodi ON prodi.idp = mahasiswa.idp
GROUP BY
prodi.idp, `status`.idm

实际结果

Hukum   0   1   0   0   0   0   0   0
Hukum   0   0   0   0   0   1   0   0
Hukum   0   1   0   0   0   0   0   0
Ekonomi 0   0   0   1   0   0   0   0
Ekonomi 0   0   0   1   0   0   0   0

想要的结果

Hukum   0   2   0   0   0   1   0   0
Ekonomi 0   0   0   2   0   0   0   0

任何想法如何让这个查询工作？

巴拉克里希南

查询需要写成

SELECT
    prodi.namaprodi, 
    SUM(Sem1) as Sem1,
    SUM(Sem2) as Sem2,
    SUM(Sem3) as Sem3,
    SUM(Sem4) as Sem4,
    SUM(Sem5) as Sem5,
    SUM(Sem6) as Sem6,
    SUM(Sem7) as Sem7,
    SUM(Sem8) as Sem8
FROM mahasiswa
INNER JOIN 
(SELECT status.idm, 
    (case when count(`status`.idsm)='1' then 1 else 0 end) as 'Sem1', 
    (case when count(`status`.idsm)='2' then 1 else 0 end) as 'Sem2', 
    (case when count(`status`.idsm)='3' then 1 else 0 end) as 'Sem3', 
    (case when count(`status`.idsm)='4' then 1 else 0 end) as 'Sem4', 
    (case when count(`status`.idsm)='5' then 1 else 0 end) as 'Sem5', 
    (case when count(`status`.idsm)='6' then 1 else 0 end) as 'Sem6', 
    (case when count(`status`.idsm)='7' then 1 else 0 end) as 'Sem7', 
    (case when count(`status`.idsm)='8' then 1 else 0 end) as 'Sem8' 
    FROM `status` 
    INNER JOIN mahasiswa ON mahasiswa.idm = `status`.idm 
    GROUP BY `status`.idm 
)SemCount
    ON mahasiswa.idm = SemCount.idm
INNER JOIN prodi ON prodi.idp = mahasiswa.idp
GROUP BY prodi.idp

我检查了查询，它给出了所需的结果。

当您需要按多个列进行分组时，在子查询中创建一个组，在主查询中创建另一个组

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。