前端之家

  1. 首页
  2. 问答

如何合并2选择而不重复

• 问答

重复的问题

是否可以在使用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

我尝试使用工会

$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...

非常感谢任何帮助，谢谢大家！

chenzhaoxia 回答：如何合并2选择而不重复

您的想法正确。但是正如其他人所说，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,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
codeigniterdatabasemysqlphpphpmyadmin
本文链接：https://www.f2er.com/3132487.html

大家都在问