這是我的桌子我想創(chuàng)建這個(gè)結(jié)果我可以在我的模型中創(chuàng)建它嗎？這是我的模型：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;請你的幫助
3 回答
LEATH
TA貢獻(xiàn)1936條經(jīng)驗(yàn) 獲得超7個(gè)贊
你的模式
詢問
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
輸出
青春有我
TA貢獻(xiàn)1784條經(jīng)驗(yàn) 獲得超8個(gè)贊
這是一個(gè)可以解決問題的 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;
我不知道您的查詢生成器是否可行。也許將其添加為原始查詢或?qū)蓚€(gè)查詢的結(jié)果粘合在一起。
GCT1015
TA貢獻(xiàn)1827條經(jīng)驗(yàn) 獲得超4個(gè)贊
它不是最干凈的，但這應(yīng)該有效：
$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;
}
- 3 回答
- 0 關(guān)注
- 151 瀏覽
添加回答
舉報(bào)
0/150
提交
取消