Vendor与events_todo表关联查询：无法正确统计Completed状态计数

现有数据表 #

Vendor表 #

ID  | userid| address | Country
1   | 10     | NY     | US
2   | 20     | Mumbai | INDIA

events_todo表 #

ID  | events_id| vendor| status
1   | 1        | 10    | Completed
2   | 2        | 20    | Inprogress

需求 #

将两张表基于Vendor.userid与events_todo.vendor进行左连接，获取所有Vendor表数据，同时新增Count列：当status为Completed时显示1，否则显示0。期望结果如下：

ID  | userid | address | Country | events_id | status     | Count
1   | 10     | NY     | US       |  1         | Completed |  1
2   | 20     | Mumbai | INDIA    |  2         | Inprogress|  0

问题 #

已编写如下SQL查询，但Count列始终显示0，无法正确统计状态为Completed的事件数量：

SELECT `vendors`.`id`, `vendors`.`userid`, `vendors`.`address`,`vendors`.`country` AS `updatedAt`, `vendors`.`userid` IN (SELECT sum(events_todo.status) AS completed FROM events_todo ) AS `completed`, `events_todo`.`id` AS `events_todo.id`, `events_todo`.`events_id` AS `events_todo.events_id`, `events_todo`.`category` `events_todo.vendor`, `events_todo`.`created_by` `events_todo.status` FROM `vendors` AS `vendors` LEFT OUTER JOIN `events_todo` AS `events_todo` ON `vendors`.`userid` = `events_todo`.`vendor` WHERE (`vendors`.`city` LIKE '%%' AND `vendors`.`state` LIKE '%%' AND `vendors`.`country` LIKE '%%') AND events_todo.status IS NOT NULL

修正后的SQL #

原SQL存在子查询逻辑错误、列别名映射混乱、WHERE条件过滤左连接有效数据等问题，以下是修正后的查询语句：

基础版本（满足核心需求） #

SELECT
    v.id,
    v.userid,
    v.address,
    v.Country,
    et.events_id,
    et.status,
    CASE WHEN et.status = 'Completed' THEN 1 ELSE 0 END AS Count
FROM Vendor v
LEFT JOIN events_todo et ON v.userid = et.vendor

保留模糊过滤的版本 #

如果需要保留原查询中对city、state、country的模糊匹配条件，可使用以下语句：

SELECT
    v.id,
    v.userid,
    v.address,
    v.Country,
    et.events_id,
    et.status,
    CASE WHEN et.status = 'Completed' THEN 1 ELSE 0 END AS Count
FROM Vendor v
LEFT JOIN events_todo et ON v.userid = et.vendor
WHERE v.city LIKE '%%' 
  AND v.state LIKE '%%' 
  AND v.Country LIKE '%%'

关键修正点 #

1. 修正Count列逻辑：用CASE语句直接判断当前行的status值，精准实现“Completed显示1，否则0”的需求，替代原查询中子查询的错误统计方式。
2. 保留左连接完整性：移除原WHERE条件中的events_todo.status IS NOT NULL，避免过滤掉无对应事件的Vendor数据，符合“获取所有Vendor表数据”的要求。
3. 清理错误映射：删除原查询中混乱的列别名（如将country映射为updatedAt、category映射为events_todo.vendor等错误操作），保留需求所需字段。
4. 简化可读性：用v和et作为表别名，提升查询语句的可读性。

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