我如何在代码点火器模型中编写简单的mysql查询

Question

SELECT DISTINCT 
SUBSTRING_INDEX(SUBSTRING_INDEX(process_disposition.sub_dispo, ',', numbers.n), ',', -1) name
FROM
(SELECT 1 n UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN process_disposition
ON CHAR_LENGTH(process_disposition.sub_dispo)
-CHAR_LENGTH(REPLACE(process_disposition.sub_dispo, ',', ''))>=numbers.n-1

Answer 1

你可以这样写

$query = $this->db->query("
SELECT DISTINCT SUBSTRING_INDEX
(SUBSTRING_INDEX(process_disposition.sub_dispo, ',', numbers.n), ',', -1)name FROM 
(SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)numbers
INNER JOIN process_disposition ON CHAR_LENGTH(process_disposition.sub_dispo)
-CHAR_LENGTH(REPLACE(process_disposition.sub_dispo, ',', ''))>=numbers.n-1"
);
if ($query->num_rows() > 0) {
return $query->result();
} else {
return false;
}

Answer 2

这样就可以了。

$this->db->select("DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(process_disposition.sub_dispo, ',', numbers.n), ',', -1) name");
$this->db->from("(SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers")
$this->db->join("process_disposition", "CHAR_LENGTH(process_disposition.sub_dispo)-CHAR_LENGTH(REPLACE(process_disposition.sub_dispo, ',', ''))>=numbers.n-1", "INNER");
$result = $this->db->get();