>我有一个表来存储所有代理活动,如下所示,
+----+---------+-------------+-------------+
| id | subject | type | relation_id |
+----+---------+-------------+-------------+
| 1 | Call | lead | 25 |
| 2 | Visit | Opportunity | 25 |
+----+---------+-------------+-------------+
怎么可能做出这样的观点。+----+---------+-----------+------------------+
| id | Subject | lead | Opportunity |
+----+---------+-----------+------------------+
| 1 | Call | lead_name | |
| 2 | Visit | | Opportunity_name |
+----+---------+-----------+------------------+
您需要使用 CASE
来获取所需的结果,如下所示:
SELECT id,subject,
CASE WHEN type='lead' THEN 'lead_name' ELSE '' END as lead,
CASE WHEN type='Opportunity' THEN 'Opportunity_name' ELSE '' END as Opportunity
FROM mytable
$this->db->join('Opportunity','Opportunity.id = store_all_agent.relation_id','left');
$query = $this->db->get('store_all_agent');
return $query->result_array();
参考 : https://www.codeigniter.com/userguide2/database/active_record.html