mysql - "SELECT"中的 "FROM"命令具有子 "SELECT"codeigniter

Question

例如我有这样的查询。

SELECT 
   id, 
   SUM( CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
    SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
FROM 
   (SELECT 
      id_kondisi as id, 
      tgl_kondisi,
      nilai_potensi_kerugian AS kl 
    FROM 
      laporan_kondisi 
    UNION ALL 
    SELECT 
       id_sub_kondisi, 
       tgl_kondisi, 
       nilai_potensi_kerugian 
    FROM 
       laporan_kondisi ) 
    merged_table 
    GROUP BY id 
    ORDER BY cast(id as char)

如何使用查询 codeigniter 函数执行此操作？

据我所知，我必须这样做

$this->db->select('id, SUM( CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 ');
$this->db->from('(SELECT id_kondisi as id, tgl_kondisi,nilai_potensi_kerugian AS kl FROM laporan_kondisi UNION ALL SELECT id_sub_kondisi, tgl_kondisi, nilai_potensi_kerugian FROM laporan_kondisi ) merged_table ');
$this->db->group_by('id');
$this->db->order_by('id as char');

有更好的方法让我不必像这样提出查询吗？

$this->db->from('(SELECT id_kondisi as id,
tgl_kondisi,nilai_potensi_kerugian AS kl FROM laporan_kondisi UNION ALL
SELECT id_sub_kondisi, tgl_kondisi, nilai_potensi_kerugian FROM
laporan_kondisi ) merged_table ');

Answer 1

您可以通过此查询创建 View :

CREATE VIEW myview AS 
SELECT 
   id, 
   SUM( CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
    SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
FROM 
   (SELECT 
      id_kondisi as id, 
      tgl_kondisi,
      nilai_potensi_kerugian AS kl 
    FROM 
      laporan_kondisi 
    UNION ALL 
    SELECT 
       id_sub_kondisi, 
       tgl_kondisi, 
       nilai_potensi_kerugian 
    FROM 
       laporan_kondisi ) 
    merged_table 
    GROUP BY id 
    ORDER BY cast(id as char);

然后您可以在 CodeIgniter 代码中使用简单的 SELECT:

$this->db->select('id, tot, sum_kl2 ');
$this->db->from('myview');

另外，为什么在 ORDER BY 中使用 CAST(ID as CHAR) 而不是仅 ORDER BY ID？