php - 选择 MAX CASE WHEN 查询

Question

我有 table ，它们是:tb_penelaah

-----------------------------
id_penelaah | nama_penelaah |
-----------------------------
1           | penelaah 1    |
2           | penelaah 2    |
-----------------------------

tb_技能

----------------------------
id_sill | nama_skill       |
----------------------------
1       | skill 1          |
2       | skill 2          |
----------------------------

tb_skill_penelaah

-----------------------------------------
skill   | penelaah  | status  | priority|
-----------------------------------------
1       | 1         | A       |  1      |
2       | 1         | B       |  1      |
1       | 2         | A       |  1      |
2       | 2         | A       |  2      |
----------------------------------------

所以我想在这样的页面中回显数据(在 PHP Codeigniter 中):

Penelaah 1 的详细信息:

Nama Penelaah : Penelaah 1
Skill A 1 : skill 1
Skill A 2 : -
Skill B 1 : skill 2
SKill B 2 : -

Penelaah 2 的详细信息:

Nama Penelaah : Penelaah 2
Skill A 1 : skill 1
Skill A 2 : skill 2
Skill B 1 : -
SKill B 2 : -

我尝试过这样的查询:

function det_skill_pen($id){
        $this->db
        ->query("SELECT a.nama_penelaah,a.id_penelaah,
            MAX(CASE WHEN b.p_status = 'A' AND b.p_urutan = 1 THEN c.nama_skill END) 'SKILL_A1',
            MAX(CASE WHEN b.p_status = 'A' AND b.p_urutan = 2 THEN c.nama_skill END) 'SKILL_A2',
            MAX(CASE WHEN b.p_status = 'B' AND b.p_urutan = 1 THEN c.nama_skill END) 'SKILL_B1',
            MAX(CASE WHEN b.p_status = 'B' AND b.p_urutan = 2 THEN c.nama_skill END) 'SKILL_B2'
            FROM tb_penelaah a
            LEFT JOIN tb_skill_p b ON b.p_penelaah = a.id_penelaah
            LEFT JOIN tb_skill c ON c.id_skill = b.p_skill
            GROUP BY a.id_penelaah
            ORDER BY id_penelaah");
        $query=$this->db->get();
        return $query;
    }

但是发生了这样的错误

A Database Error Occurred

No tables used

SELECT *

有人可以帮忙吗？我不太擅长sql查询

Answer 1

解决了!!!

我已经编辑了我的 Codeigniter 模型:

function det_skill_pen($id){
        $this->db
        ->query("SELECT a.nama_penelaah,a.id_penelaah,
            MAX(CASE WHEN b.p_status = 'A' AND b.p_urutan = 1 THEN c.nama_skill END) 'SKILL_A1',
            MAX(CASE WHEN b.p_status = 'A' AND b.p_urutan = 2 THEN c.nama_skill END) 'SKILL_A2',
            MAX(CASE WHEN b.p_status = 'B' AND b.p_urutan = 1 THEN c.nama_skill END) 'SKILL_B1',
            MAX(CASE WHEN b.p_status = 'B' AND b.p_urutan = 2 THEN c.nama_skill END) 'SKILL_B2'
            FROM tb_penelaah a
            LEFT JOIN tb_skill_p b ON b.p_penelaah = a.id_penelaah
            LEFT JOIN tb_skill c ON c.id_skill = b.p_skill
            GROUP BY a.id_penelaah
            ORDER BY id_penelaah");
        $query=$this->db->get();
        return $query;
    }

对此:

function det_skill_pen($id){
        $query=$this->db->query("SELECT *,
            MAX(CASE WHEN b.p_status = 'A' AND b.p_urutan = 1 THEN c.nama_skill END) 'SKILL_A1',
            MAX(CASE WHEN b.p_status = 'A' AND b.p_urutan = 2 THEN c.nama_skill END) 'SKILL_A2',
            MAX(CASE WHEN b.p_status = 'B' AND b.p_urutan = 1 THEN c.nama_skill END) 'SKILL_B1',
            MAX(CASE WHEN b.p_status = 'B' AND b.p_urutan = 2 THEN c.nama_skill END) 'SKILL_B2'
            FROM tb_penelaah a
            LEFT JOIN tb_skill_p b ON b.p_penelaah = a.id_penelaah
            LEFT JOIN tb_skill c ON c.id_skill = b.p_skill
            WHERE a.id_penelaah = $id
            GROUP BY a.id_penelaah
            ORDER BY id_penelaah");
        return $query;
    }

现在工作正常，感谢@Raptor的回答和建议