Mysql复杂查询如果没有找到记录则生成计数为零的所有表

Question

我有一个复杂的查询。而且我卡住了位置:(。这是我的 table 。用户:

CREATE TABLE `users` (
  `id` tinyint(4) NOT NULL,
  `office_name` varchar(255) DEFAULT NULL,
  `district_id` int(10) DEFAULT NULL,
  `upazilla_id` int(10) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `type` varchar(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `service` (
  `id` int(10) NOT NULL,
  `recipient_number` int(50) NOT NULL,
  `date` date NOT NULL,
  `office_id` int(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL,
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `service_list` (
  `id` int(3) NOT NULL,
  `service_name` varchar(50) NOT NULL,
  `del_status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `service_transaction` (
  `id` int(10) NOT NULL,
  `service_transaction_id` int(50) NOT NULL,
  `service_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这里，service-id = service_transaction.service_transaction_id。

我写了一个查询:

select users.id, users.office_name,service_transaction.service_id , COUNT(service_transaction.service_id) as service_total
from users
LEFT JOIN service on users.id = service.office_id
LEFT JOIN service_transaction on service_transaction.service_transaction_id = service.id
WHERE( users.del_status = 0 and users.type='agency')
GROUP BY users.office_name , users.id, service_transaction.service_id 

它返回:

id   office_name  Service_id  service_total
=============================================
2    Ctg Office      2             2
2    Ctg Office      3             4
2    Ctg Office      4             3
7    Dhaka Office   NULL           0

但我的愿望输出是:

id   office_name  Service_id  service_total
=============================================
2    Ctg Office      2             2
2    Ctg Office      3             4
2    Ctg Office      4             3
2    Ctg Office      5             0
2    Ctg Office      6             0

7    Dhaka Offc      2             0
7    Dhaka Offc      3             0
7    Dhaka Offc      4             0
7    Dhaka Offc      5             0
7    Dhaka Offc      6             0

这意味着，我必须显示每个办公室下的所有服务，如果没有服务，计数应该为零。

Answer 1

select temp.id office_id , temp.office_name, temp.svcic ,count(service_transaction.service_id) as service_total, count(recipient.id) as total_count from (select users.id, users.office_name ,service_list.id svcic from users,service_list WHERE users.del_status = 0 and users.type='agency' )  temp LEFT JOIN service on temp.id = service.office_id and service.del_status=0 LEFT JOIN service_transaction on service_transaction.service_id = temp.svcic and service_transaction.service_transaction_id=service.id right outer JOIN recipient on recipient.office_id = temp.id group by temp.id , temp.office_name, temp.svcic ORDER BY temp.id,temp.office_name

请尝试上面的代码。为了更具可读性，您应该保持意图