如何进行多重计数、分组、加入以及在哪里使用 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_total

FROM (

    SELECT a.name as name, count(a.name) as a_total 

    FROM test.table_a as a

    group by a.name

) as table_a

INNER JOIN (

    SELECT b.name as name, count(b.name) as b_total 

    FROM test.table_b as b

    group by b.name

) as table_b

ON 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 tablea

LEFT 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;

}