mysql - 统计每个部门的所有连接用户

Question

我有一个柜员，可以将其视为每个服务部门的用户

我有这段代码，它输出的正是我想要的内容，除了 active_tellers 字段。

SELECT service_info.name AS service_name, 
service_info.current_serving AS current_service, 
service_info.last_printed AS last_printed, 
service_info.remaining_queue AS remaining_queue, 
AVG(teller_log.duration), 
service_info.active_tellers AS active_teller 
FROM user_info 
JOIN teller_info 
ON user_info.teller_id = teller_info.teller_id 
JOIN service_info 
ON service_info.service_id = teller_info.service_id 
JOIN teller_log 
ON user_info.user_id = teller_log.user_id 
GROUP BY 
service_info.name

它输出这个

这是我完整的数据库架构

正如您在 teller_info 表 的数据库架构中看到的，我有字段 status，它是 ENUM [Connected, Disconnected]这是我想要获取的字段，如果 teller_1 位于 Cashier 下并且它是 Connected 它应该service_name Cashier 中的输出 active_teller 为 1

我遇到问题的代码部分是这样的

service_info.active_tellers AS active_teller 

我已经尝试过

(SELECT COUNT(teller_info.teller_id) FROM teller_info WHERE status = "Connected") as a

但是输出不正确

编辑:

输出

show create table service_info

CREATE TABLE `service_info` (
  `service_id` int(11) NOT NULL AUTO_INCREMENT,
  `service_num` int(2) unsigned zerofill NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `starting_num` int(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `ending_num` int(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `current_serving` int(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `last_printed` int(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `remaining_queue` int(10) NOT NULL DEFAULT '0',
  `active_tellers` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`service_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT='Table for service (Cashier, information, repair)'

Answer 1

尝试对服务中连接的柜员进行计数

SUM(IF(teller_info.status = 'Connected', 1, 0)) AS active_tellers