如何在CI 3.x中实现SQL子查询?

Question

我有一个像下面这样的SQL查询:

SELECT a.name_prov AS prov,city,a.cases 
FROM 
(SELECT c.name_prov,b.city,a.cases, 
ROW_NUMBER() OVER (PARTITION BY c.name_prov ORDER BY CAST(a.cases AS INT)DESC) AS rn 
FROM tb_cases a  
JOIN tb_city b ON a.id_city = b.id_city 
JOIN tb_prov c ON a.id_prov = c.id_prov 
ORDER BY c.name_prov 
) a 
ORDER BY a.name_prov,rn

如何在CodeIgniter ver 3中编写上面的选择语句?X活动记录?

Answer 1

我建议你去CodeIgniter论坛看看这类问题。

$subQuery = $this->db->select()
->....
->get_compiled_select();
$this->db->select('*')
->from("($subQuery) as t", false); 

来源:https://forum.codeigniter.com/showthread.php?tid=70943

同样，对于您提供的查询，您不需要子查询:

SELECT c.name_prov as prov, b.city, a.cases, ROW_NUMBER() OVER (PARTITION BY c.name_prov ORDER BY CAST(a.cases AS INT) DESC) AS rn 
FROM tb_cases a  
JOIN tb_city b ON a.id_city = b.id_city 
JOIN tb_prov c ON a.id_prov = c.id_prov
ORDER BY prov, rn

这应该等同于你的查询。