迭代问题
是否可以在使用codeigniter Mysql的单个查询中使用codeigniter获得此输出?
oid | count(waiters) as total_waiters
----+-------------------------------
1 | 1 <-- john will be count as 1 even if assigned to 2 room
2 | 1
3 | 2 <-- count is 2 because different waiters are assigned with different room
4 | 0
订货表
oid | name
----+-------
1 | aa
2 | bb
3 | cc
4 | dd
房间桌子
Rid | oid | waiter_assigned
----+-------+-----------------
1 | 1 | john
2 | 1 | john
3 | 2 | john
4 | 3 | mike
5 | 3 | dude
我试着用union
$this->db->select('o.oid, "" AS tot_book_thera');
$this->db->from('order o');
$query1 = $this->db->get_compiled_select();
$this->db->select('r.oid, count(r.waiter_assigned) AS total_waiters');
$this->db->from('room r');
$this->db->group_by('r.waiter_assigned');
$query2 = $this->db->get_compiled_select();
但我知道。。。
oid | count(waiters) as total_waiters
----+-------------------------------
1 | 1
2 | 1
3 | 2
1 | '' <-- not sure how to combine it with the 1st or how to exclude this or remove this...
非常感谢你们的帮助,谢谢!
最佳答案
你的想法是对的。但正如其他人所说,GROUP BY是你在这里最好的朋友。另外,使用DISTINCT来消除对同一个服务生的两次计数。这就是你的代码应该是什么样子
// An inner select query whose purpose is to count all waiter per room
// The key here is to group them by `oid` since we are interested in the order
// Also, in the count(), use DISTINCT to avoid counting duplicates
$this->db->select('room.oid, count(DISTINCT room.waiter_assigned) AS total_waiters');
$this->db->from('room');
$this->db->group_by('room.oid');
$query1 = $this->db->get_compiled_select();
// If you run $this->db->query($query1)->result(); you should see
oid | total_waiters
----+-------------------------------
1 | 1
2 | 1
3 | 2
// This is how you would use this table query in a join.
// LEFT JOIN also considers those rooms without waiters
// IFNULL() ensures that you get a 0 instead of null for rooms that have no waiters
$this->db->select('order.oid, order.name, IFNULL(joinTable.total_waiters, 0) AS total_waiters');
$this->db->from('order');
$this->db->join('('.$query1.') joinTable', 'joinTable.oid = order.oid', 'left');
$this->db->get()->result();
// you should see
oid | name | total_waiters
----+-----------+-------------------------
1 | aa | 1
2 | bb | 1
3 | cc | 2
4 | dd | 0
下面是原始SQL语句
SELECT order.oid, order.name, IFNULL(joinTable.total_waiters, 0) AS total_waiters
FROM order
LEFT JOIN (
SELECT room.oid, count(DISTINCT room.waiter_assigned) AS total_waiters
FROM room
GROUP BY room.oid
) joinTable ON joinTable.oid = order.oid
关于php - 如何合并2选择而不重复,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58780390/