MySQL三表关联SUM求和错误排查：含Having子句的查询问题

阿华AIGC实验室

2026-7-2

问题：多表关联查询时活动分数总和计算错误

表结构与测试数据

accounts表

CREATE TABLE `accounts` (
  `id` int(10) UNSIGNED NOT NULL,
  `companyName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_duplicate` enum('yes','no') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'no',
  `cmpCodes` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` enum('yes','no') COLLATE utf8mb4_unicode_ci DEFAULT 'no',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `accounts` (`id`, `companyName`, `is_duplicate`, `cmpCodes`, `is_deleted`, `created_at`, `updated_at`) VALUES
(1024, 'Vodafone Germany','no', 'imli02-0323', 'no', '2023-10-20 09:36:02', '2021-11-18 15:35:39'),
(1336, 'Microsoft', 'no', 'imli02-0323', 'no', '2023-11-23 20:02:03', '2021-12-20 14:16:29'),
(1234, 'Microsoft test','no', 'imli02-0323', 'no', '2023-11-23 20:02:03', '2021-12-20 14:16:29');

contacts表

CREATE TABLE `contacts` (
  `id` int(11) NOT NULL,
  `accountId` int(11) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `cmpMultiple` text DEFAULT NULL,
  `is_duplicate` enum('yes','no') NOT NULL DEFAULT 'no',
  `is_deleted` enum('yes','no') DEFAULT 'no',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `contacts` (`id`, `accountId`, `name`, `cmpMultiple`, `is_duplicate`, `is_deleted`, `created_at`, `updated_at`) VALUES
(4543, 1336, 'alex', 'imli02-0323', 'no', 'no', '2023-08-15 15:13:22', '2023-11-20 10:24:44'),
(4545, 1024, 'julie', 'imli02-0323', 'no', 'no', '2023-08-16 10:26:22', '2024-01-08 08:33:23'),
(4742, 1336, 'Matt', 'imli02-0323', 'no', 'no', '2023-11-14 11:23:21', '2023-11-23 20:02:03'),
(4744, 1336, 'Martin', 'imli02-0323', 'no', 'no', '2023-11-14 11:23:21', '2023-11-15 16:39:33'),
(4743, 1336, 'Andrew', 'imli02-0323', 'no', 'no', '2023-11-14 11:23:22', '2023-11-23 19:02:48'),
(3434, 1234, 'jack', 'imli02-0323', 'no', 'no', '2023-08-16 10:26:22', '2024-01-07 12:09:04');

contact_activity表

CREATE TABLE `contact_activity` (
  `id` int(11) NOT NULL,
  `contactID` int(11) NOT NULL,
  `accountId` int(11) DEFAULT NULL COMMENT 'PD org id',
  `cmpCode` varchar(255) DEFAULT NULL,
  `activityScore` int(11) DEFAULT NULL,
  `activityDate` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `contact_activity` (`id`, `contactID`, `accountId`, `cmpCode`, `activityScore`, `activityDate`, `created_at`) VALUES
(2442, 4543, 1336, 'imli02-0323', 3, '2023-08-15 16:13:35', '2023-08-15 17:13:36'),
(2451, 4545, 1024, 'imli02-0323', 8, '2023-08-16 11:27:24', '2023-08-16 12:27:25'),
(2695, 4543, 1336, 'imli02-0323', 3, '2023-10-09 16:24:50', '2023-10-09 17:24:51'),
(3116, 0, 1024, 'imli02-0323', 4, '2023-12-27 12:55:47', '2023-12-29 13:47:53');

错误的查询语句

SELECT `accounts`.`companyName` AS `name`,
      `accounts`.`id` AS `accountId`,
    SUM(contact_activity.activityScore) AS totalActivityScore,
    contact_activity.id AS activityid
    FROM
        `accounts`
    LEFT JOIN `contacts` ON `accounts`.`id` = `contacts`.`accountId` AND(
    `contacts`.`cmpMultiple` = 'imli02-0323' AND( `accounts`.`cmpCodes` = 'imli02-0323' OR accounts.cmpCodes = '' OR accounts.cmpCodes IS NULL
            )
        ) AND `contacts`.`is_duplicate` = 'no' AND `contacts`.`is_deleted` = 'no'
    INNER JOIN `contact_activity` ON
        (
            `contact_activity`.`contactID` = `contacts`.`id` AND `contact_activity`.`cmpCode` = 'imli02-0323' AND `contact_activity`.`contactID` IS NOT NULL
        ) OR(
            contact_activity.accountId = accounts.id AND contact_activity.cmpCode = 'imli02-0323' AND `contact_activity`.`accountId` IS NOT NULL
        )
    WHERE
        `accounts`.`is_duplicate` = 'no' AND `accounts`.`is_deleted` = 'no'  AND `contact_activity`.`activityScore` != ''
    GROUP BY
        `contact_activity`.`accountId`,
        `accounts`.`id`
    HAVING
        SUM(contact_activity.activityScore) >= 6;

当前错误结果

name	accountId	totalActivityScore	activityid
Vodafone Germany	1024	12	2451
Microsoft	1336	24	2442

期望正确结果

name	accountId	totalActivityScore	activityid
Vodafone Germany	1024	12	2451
Microsoft	1336	6	2442

问题原因

查询中使用LEFT JOIN contacts后，contact_activity的每条记录会和该账户下所有符合条件的联系人匹配，形成笛卡尔积。以Microsoft（accountId=1336）为例，该账户有4个有效联系人，每条活动记录会被关联4次，原本2条记录的分数总和3+3=6，被重复计算4次后得到24。

修正后的查询语句

SELECT 
    a.companyName AS name,
    a.id AS accountId,
    SUM(ca.activityScore) AS totalActivityScore,
    MIN(ca.id) AS activityid -- 取该账户最早的活动ID，可根据需求调整
FROM accounts a
JOIN (
    -- 先筛选并关联所有有效活动记录，确保每条活动只对应一个账户
    SELECT 
        ca.id,
        ca.activityScore,
        -- 优先取联系人关联的账户，否则取活动直接关联的账户
        COALESCE(c.accountId, ca.accountId) AS accountId
    FROM contact_activity ca
    LEFT JOIN contacts c 
        ON ca.contactID = c.id 
        AND c.cmpMultiple = 'imli02-0323' 
        AND c.is_duplicate = 'no' 
        AND c.is_deleted = 'no'
    WHERE ca.cmpCode = 'imli02-0323'
        AND ca.activityScore IS NOT NULL
        -- 确保活动属于有效账户
        AND (c.accountId IS NOT NULL OR ca.accountId IS NOT NULL)
    -- 去重，避免同一活动被多次关联
    GROUP BY ca.id, ca.activityScore, COALESCE(c.accountId, ca.accountId)
) ca ON ca.accountId = a.id
WHERE a.is_duplicate = 'no' 
    AND a.is_deleted = 'no'
    AND (a.cmpCodes = 'imli02-0323' OR a.cmpCodes = '' OR a.cmpCodes IS NULL)
GROUP BY a.id, a.companyName
HAVING SUM(ca.activityScore) >= 6;