按Orgdocumenttype分组统计Document.id去重计数结果异常求助

问题原因 #

原SQL统计结果不符合预期的核心原因有两个：

1. Document表关联条件缺失：原SQL中Document仅通过Orgdocumenttype.id关联，未限定Document.orderId与当前订单的OrderDocumentType.orderId匹配，导致统计了所有属于该文档类型的Document，而非当前订单（orderId=474019）下的文档。
2. 多表连接产生冗余行：Document与DocumentImage、Annotation的左连接会生成笛卡尔积（一个文档对应多个图片/批注时，同一文档ID会重复出现），即使使用COUNT(DISTINCT)，也会因为关联范围错误导致统计值偏大。

修改后的SQL语句 #

SELECT 
    `OrderDocumentType`.`orderId` AS `orderId`,
    `OrderDocumentType`.`status` AS `status`,
    `Orgdocumenttype`.`name` AS `Orgdocumenttype__name`,
    `Orgdocumenttype`.`needsApproval` AS `Orgdocumenttype__needsApproval`,
    `Order`.`status` AS `Order__status`,
    (SELECT GROUP_CONCAT(DISTINCT `Annotation`.`text`)
     FROM `DocumentImage` 
     LEFT JOIN `Annotation` ON `DocumentImage`.`id` = `Annotation`.`documentImageId`
     WHERE `DocumentImage`.`documentId` IN (SELECT `id` FROM `Document` WHERE `Document`.`orgDocumentTypeId` = `Orgdocumenttype`.`id` AND `Document`.`orderId` = `OrderDocumentType`.`orderId`)) AS `Annotation__text`,
    (SELECT COUNT(DISTINCT `Document`.`id`)
     FROM `Document` 
     WHERE `Document`.`orgDocumentTypeId` = `Orgdocumenttype`.`id` AND `Document`.`orderId` = `OrderDocumentType`.`orderId`) AS `document_count`,
    `Orgdocumenttype`.`id` AS `orgDocTypeId`
FROM `OrderDocumentType`
LEFT JOIN `OrgDocumentType` AS `Orgdocumenttype` ON `OrderDocumentType`.`orgDocumentTypeId` = `Orgdocumenttype`.`id` 
LEFT JOIN `Order` AS `Order` ON `OrderDocumentType`.`orderId` = `Order`.`id` 
WHERE `OrderDocumentType`.`orgId` = 2556
    AND (`Order`.`status` <> 'CANCELLED_BY_HAVEN' OR `Order`.`status` IS NULL)
    AND (`Order`.`status` <> 'CANCELLATION_REQUESTED_BY_SHIPPER' OR `Order`.`status` IS NULL) 
    AND (`Order`.`status` <> 'CANCELLED_BY_PROVIDER' OR `Order`.`status` IS NULL) 
    AND (`Order`.`status` <> 'CANCELLED_BY_SHIPPER' OR `Order`.`status` IS NULL) 
    AND (`Order`.`status` <> 'EXPIRED' OR `Order`.`status` IS NULL) 
    AND `Orgdocumenttype`.`needsApproval` = TRUE 
    AND `OrderDocumentType`.`orderId` = 474019
GROUP BY `Orgdocumenttype`.`id`, `OrderDocumentType`.`orderId`, `OrderDocumentType`.`status`, `Orgdocumenttype`.`name`, `Orgdocumenttype`.`needsApproval`, `Order`.`status`

修改说明 #

1. 限定Document的订单范围：在子查询中添加Document.orderId = OrderDocumentType.orderId，确保仅统计当前订单下的文档。
2. 分离聚合逻辑：将Annotation的聚合和Document的统计放在独立子查询中，避免多表连接产生的笛卡尔积干扰统计结果。
3. 完善GROUP BY子句：根据SQL标准，SELECT中所有非聚合列都需要包含在GROUP BY中，避免因数据库模式差异导致的结果异常。

补充：包含所有符合条件的文档类型 #

如果需要包含所有属于当前组织且需要审批的文档类型（比如预期结果中的"Booking Confirmation"），可以将主表切换为OrgDocumentType，确保不会遗漏未关联到OrderDocumentType的类型：

SELECT 
    474019 AS `orderId`,
    `OrderDocumentType`.`status` AS `status`,
    `Orgdocumenttype`.`name` AS `Orgdocumenttype__name`,
    `Orgdocumenttype`.`needsApproval` AS `Orgdocumenttype__needsApproval`,
    `Order`.`status` AS `Order__status`,
    (SELECT GROUP_CONCAT(DISTINCT `Annotation`.`text`)
     FROM `Document`
     LEFT JOIN `DocumentImage` ON `Document`.`id` = `DocumentImage`.`documentId`
     LEFT JOIN `Annotation` ON `DocumentImage`.`id` = `Annotation`.`documentImageId`
     WHERE `Document`.`orgDocumentTypeId` = `Orgdocumenttype`.`id` AND `Document`.`orderId` = 474019) AS `Annotation__text`,
    (SELECT COUNT(DISTINCT `Document`.`id`)
     FROM `Document` 
     WHERE `Document`.`orgDocumentTypeId` = `Orgdocumenttype`.`id` AND `Document`.`orderId` = 474019) AS `document_count`,
    `Orgdocumenttype`.`id` AS `orgDocTypeId`
FROM `OrgDocumentType` AS `Orgdocumenttype`
LEFT JOIN `OrderDocumentType` ON `OrderDocumentType`.`orgDocumentTypeId` = `Orgdocumenttype`.`id` AND `OrderDocumentType`.`orderId` = 474019 AND `OrderDocumentType`.`orgId` = 2556
LEFT JOIN `Order` ON `OrderDocumentType`.`orderId` = `Order`.`id` 
WHERE `Orgdocumenttype`.`needsApproval` = TRUE 
    AND `Orgdocumenttype`.`orgId` = 2556
    AND (`Order`.`status` <> 'CANCELLED_BY_HAVEN' OR `Order`.`status` IS NULL)
    AND (`Order`.`status` <> 'CANCELLATION_REQUESTED_BY_SHIPPER' OR `Order`.`status` IS NULL) 
    AND (`Order`.`status` <> 'CANCELLED_BY_PROVIDER' OR `Order`.`status` IS NULL) 
    AND (`Order`.`status` <> 'CANCELLED_BY_SHIPPER' OR `Order`.`status` IS NULL) 
    AND (`Order`.`status` <> 'EXPIRED' OR `Order`.`status` IS NULL) 
GROUP BY `Orgdocumenttype`.`id`, `OrderDocumentType`.`status`, `Orgdocumenttype`.`name`, `Orgdocumenttype`.`needsApproval`, `Order`.`status`
ORDER BY `Orgdocumenttype`.`id` ASC

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