mysql - 显示一张 MYSQL 表中的相同数据

Question

enter image description here

脚本:

SELECT 
tb_biodata.nama_lengkap as nama, 
tb_biodata.jenis_pegawai, 
tb_biodata.cabang,
tb_penilaian.tanggal,
case when tb_user.grup = 'satu' and tb_user.jabatan = 'kepegawaian' then rata_rata end AS penilai1, 
case when tb_user.grup = 'satu' and tb_user.jabatan = 'mg' then rata_rata end AS penilai2, 
case when tb_user.cabang = 'Majapahit 605' and tb_user.jabatan = 'amg' then rata_rata end AS penilai3

FROM tb_biodata 
inner join tb_penilaian ON tb_biodata.kode_biodata = tb_penilaian.kode_biodata
inner join tb_user ON tb_penilaian.kode_user = tb_user.kode_user 
where tb_biodata.jenis_pegawai = 'pegawai kependidikan'

如何将数据变成一行

Answer 1

您可以使用GROUP BY，在本例中使用GROUP_CONCAT。

假设nama_lengkap是唯一的，那么查询将如下。

如果有重复尝试在GROUP BY中使用主键

SELECT 
tb_biodata.nama_lengkap AS nama, 
tb_biodata.jenis_pegawai, 
tb_biodata.cabang,
tb_penilaian.tanggal,
GROUP_CONCAT(CASE WHEN tb_user.grup = 'satu' AND tb_user.jabatan = 'kepegawaian' THEN rata_rata END) AS penilai1, 
GROUP_CONCAT(CASE WHEN tb_user.grup = 'satu' AND tb_user.jabatan = 'mg' THEN rata_rata END) AS penilai2, 
GROUPCONCAT(CASE WHEN tb_user.cabang = 'Majapahit 605' AND tb_user.jabatan = 'amg' THEN rata_rata END) AS penilai3
FROM tb_biodata 
INNER JOIN tb_penilaian ON tb_biodata.kode_biodata = tb_penilaian.kode_biodata
INNER JOIN tb_user ON tb_penilaian.kode_user = tb_user.kode_user 
WHERE tb_biodata.jenis_pegawai = 'pegawai kependidikan'
GROUP BY nama;