如何进行多重计数、分组、加入以及在哪里使用 Codeigniter

Question

这是我的桌子

我想创建这个结果

我可以在我的模型中创建它吗？这是我的模型：

function test(){

$this->db->select('a.id, a.name, a.city, COUNT(*) AS totala');

$this->db->select('b.name, COUNT(*) AS totalb');

$this->db->from('tabela as a');

$this->db->join('a.name = b.name');

$this->db->group_by('a.name');

$this->db->order_by('a.id','DESC');

$this->db->where('city'='BDG');

return;

请你的帮助

Answer 1

你的模式询问SELECT table_a.name, table_a.a_total, table_b.b_totalFROM (    SELECT a.name as name, count(a.name) as a_total     FROM test.table_a as a    group by a.name) as table_aINNER JOIN (    SELECT b.name as name, count(b.name) as b_total     FROM test.table_b as b    group by b.name) as table_bON table_a.name = table_b.name输出

Answer 2

这是一个可以解决问题的 MySQL 查询：SELECT tablea.name as "NAME", totala as "TOTAL A", totalb as "TOTAL B"FROM (    SELECT `name`, count(*) AS totala    FROM A    WHERE city = 'BDG'    GROUP BY `NAME`) AS tableaLEFT JOIN (    SELECT `name`, count(*) AS totalb    FROM B    WHERE city = 'BDG'    GROUP BY `NAME`) AS tableb ON tablea.name = tableb.name;我不知道您的查询生成器是否可行。也许将其添加为原始查询或将两个查询的结果粘合在一起。

Answer 3

它不是最干净的，但这应该有效：$where = "city = 'BDG'";$group = "NAME";$where = "ID ASC";function test($where,$group,$order){    $this->db->select("a.name, a.city, COUNT(*) AS totala, (select count(b.id) from tableb as b where a.name = b.name) as totalb");    $this->db->from('tabela as a');    $this->db->group_by($group);    $this->db->order_by($order);    $this->db->where($where);    return;}