MySQL查询：即使无对应任务也返回所有优先级的计数

我之前也碰到过类似的需求，你的核心问题在于左连接方向搞反了，而且过滤条件的位置不对，直接导致MEDIUM这种没有对应任务的优先级被排除出结果了。咱们一步步来修正：

原SQL的问题分析 #

你当前的SQL是从task表左连接priority表，这意味着只有task中存在的优先级才会出现在结果里。但我们需要的是所有优先级类别都必须返回，哪怕没有对应任务，所以得把priority作为主表来左连接task。另外，把companyId和projectId的过滤条件放在WHERE里，会直接过滤掉左连接后task字段为NULL的行——这也是MEDIUM没出现的关键原因。

修正后的SQL语句 #

SELECT 
    p.code AS priority,
    COUNT(t.id) AS count
FROM 
    priority p
LEFT JOIN 
    task t ON p.code = t.TaskPriorityCode 
        AND t.companyId = 16 
        AND t.projectId = 4
GROUP BY 
    p.code
ORDER BY 
    CASE p.code 
        WHEN 'CRITICAL' THEN 1 
        WHEN 'HIGH' THEN 2 
        WHEN 'MEDIUM' THEN 3 
        WHEN 'LOW' THEN 4 
    END;

关键改动说明： #

• 以priority表为主表，确保所有优先级类别都会被包含在结果中
• 将companyId和projectId的过滤条件移到JOIN的ON子句中，避免过滤掉主表的行
• 使用COUNT(t.id)统计任务数：当没有对应任务时，t.id为NULL，COUNT会自动返回0；如果用COUNT(p.code)会一直返回1，因为主表的code永远非空
• 增加ORDER BY子句，让优先级按照CRITICAL>HIGH>MEDIUM>LOW的逻辑排序，结果更符合业务直觉

执行结果 #

执行后会得到你期望的完整结果：

priority | count
---------|------
CRITICAL | 4
HIGH     | 1
MEDIUM   | 0
LOW      | 1

测试用表结构与数据 #

以下是你提供的测试用SQL，方便直接验证：

CREATE TABLE `priority` ( 
    `code` varchar(20) NOT NULL, 
    `display` varchar(255) NOT NULL, 
    `description` varchar(255) DEFAULT NULL, 
    `metavalue` varchar(255) DEFAULT NULL, 
    `createdAt` datetime NOT NULL, 
    `updatedAt` datetime NOT NULL 
);
INSERT INTO `priority` VALUES 
('CRITICAL','Critical','Critical',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51'),
('HIGH','High','High',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51'),
('LOW','Low','Low',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51'),
('MEDIUM','Medium','Medium',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51');

CREATE TABLE `task` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `taskName` varchar(255) NOT NULL, 
    `description` varchar(255) DEFAULT NULL, 
    `startDt` datetime NOT NULL, 
    `finishDt` datetime DEFAULT NULL, 
    `estimatedHrs` float DEFAULT NULL, 
    `completedHrs` float DEFAULT NULL, 
    `active` tinyint(1) NOT NULL DEFAULT '1', 
    `repetitiveDays` varchar(255) DEFAULT NULL, 
    `createdAt` datetime NOT NULL, 
    `submittedDt` datetime DEFAULT NULL, 
    `closedDt` datetime DEFAULT NULL, 
    `updatedAt` datetime NOT NULL, 
    `deletedAt` datetime DEFAULT NULL, 
    `assignorId` int(11) DEFAULT NULL, 
    `assigneeId` int(11) DEFAULT NULL, 
    `projectId` int(11) NOT NULL, 
    `TaskStatusCode` varchar(20) NOT NULL, 
    `TaskPriorityCode` varchar(20) NOT NULL, 
    `companyId` int(11) NOT NULL,
    PRIMARY KEY (`id`)
);
INSERT INTO `task` VALUES 
(8,'My First Task - Fix errors','My First Task - Fix errors Description','2018-01-01 05:00:00','2018-01-01 05:00:00',100,50,1,'wed,sat,sun','2018-04-16 14:35:02','2018-04-16 15:06:56','2018-04-16 15:23:35','2018-05-13 23:43:56',NULL,13,13,4,'IN_PROGRESS','CRITICAL',16),
(9,'Second Task - again fix errors','Second Task - again fix errors','2018-01-01 05:00:00','2018-05-01 04:00:00',50,10,1,'','2018-04-16 15:35:52',NULL,NULL,'2018-04-16 15:36:57',NULL,13,13,5,'IN_PROGRESS','MEDIUM',16),
(10,'Third Task - again fix errors','Third Task - again fix errors','2018-04-01 04:00:00','2018-04-01 04:00:00',100,100,1,'','2018-04-16 15:36:36','2018-04-19 16:07:14','2018-04-19 16:07:36','2018-04-19 16:07:36',NULL,13,13,4,'CLOSED','LOW',16),
(11,'Fourth Task - again fix errors','Third Task - again fix errors','2018-01-01 05:00:00','2018-05-01 04:00:00',200,0,1,'','2018-04-16 15:37:54',NULL,NULL,'2018-04-16 15:38:32',NULL,13,13,4,'ASSIGNED','HIGH',16),
(12,'Fifth Task - again fix errors','Fifth Task - again fix errors','2018-01-01 05:00:00','2018-06-01 04:00:00',22,NULL,1,'','2018-04-16 15:39:49',NULL,NULL,'2018-04-16 15:39:49',NULL,13,13,4,'ASSIGNED','CRITICAL',16),
(13,'Fix payment',NULL,'2018-05-04 04:00:00','2018-05-11 04:00:00',40,NULL,0,'','2018-05-04 14:50:31',NULL,NULL,'2018-05-04 14:53:34','2018-05-04 14:53:34',15,15,4,'ASSIGNED','CRITICAL',16),
(14,'New','new','2018-05-01 04:00:00','0000-00-00 00:00:00',20,NULL,1,'','2018-05-13 23:44:50',NULL,NULL,'2018-05-13 23:44:50',NULL,13,13,4,'ASSIGNED','CRITICAL',16);

内容的提问来源于stack exchange，提问作者jackfrost5234