MySQL关联查询重复计数问题及去重统计方案咨询

我明白你的问题了——关联job和job_definition表后，因为一个jobcard对应多条job_definition记录，导致查询结果里每个jobcard被重复计数，最终统计数值被大幅放大。要实现每个jobcard仅统计一次，核心思路是在统计时对jobcard的唯一标识（也就是j.id）进行去重计算。

问题根源分析 #

你的原查询中，job表的jobcard字段不唯一，一个jobcard对应多个job_definition条目。当你关联job和job_definition后，会产生笛卡尔积：一条jobcard记录会被拆分成多条（对应它的每个job_definition），所以SUM(CASE...)会把这些重复条目都算进去，导致统计结果虚高。

修改后的查询语句 #

下面是调整后的SQL，通过COUNT(DISTINCT...)替代原有的SUM(CASE...)，确保每个jobcard只被统计一次：

SELECT 
  MONTHNAME(j.`date_in`) AS MONTH,
  COUNT(DISTINCT j.`id`) AS TotalBookings,
  COUNT(DISTINCT CASE WHEN j.`status` = 1 THEN j.`id` END) AS NoShows,
  COUNT(DISTINCT CASE WHEN j.`status` = 5 THEN j.`id` END) AS Deleted,
  COUNT(DISTINCT CASE WHEN jd.`division` IN(494,489,488,487,486,485,484,483,482,481,492,490,479,478,493,491,477,476,475,474,473,495,496) THEN j.`id` END) AS Services,
  COUNT(DISTINCT CASE WHEN jd.`division` IN(514,529,544,559,574,589,604,619,634,649,664,679,694,709,724,739,754,769,784,799,814,829,844,515,516,517,518,519,520,521,522,523,524,525,526,527,528,530,531,532,533,534,535,536,537,538,539,540,541,542,543,545,546,547,548,549,550,551,552, 553,554,555,556,557,558,560,561,562,563,564,565,566,567,568,569,570,571,572,573,575,576,577,578,579,580,581,582,583,584,585,586,587,588,590,591,592,593,594,595, 596,597,598,599,600,601,602,603,605,606,607,608,609,610,611,612,613,614,615,616,617,618,620,621,622,623,624,625,626,627,628,629,630,631,632,633,635,636,637,638, 639,640,641,642,643,644,645,646,647,648,650,651,652,653,654,655,656,657,658,659,660,661,662,663,665,666,667,668,669,670,671,672,673,674,675,676,677,678,680,681, 682,683,684,685,686,687,688,689,690,691,692,693,695,696,697,698,699,700,701,702,703,704,705,706,707,708,710,711,712,713,714,715,716,717,718,719,720,721,722,723, 725,726,727,728,729,730,731,732,733,734,735,736,737,738,740,741,742,743,744,745,746,747,748,749,750,751,752,753,755,756,757,758,759,760,761,762,763,764,765,766, 767,768,770,771,772,773,774,775,776,777,778,779,780,781,782,783,785,786,787,788) THEN j.`id` END) AS Repairs
FROM jobcard j
INNER JOIN (SELECT jobcard, job_definition FROM job GROUP BY jobcard, job_definition) jb 
  ON j.`id` = jb.`jobcard`
JOIN job_definition jd 
  ON jb.`job_definition` = jd.`id`
WHERE DATE(j.`date_in`) BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY MONTH(j.`date_in`);

关键调整说明 #

• TotalBookings：直接用COUNT(DISTINCT j.id)统计每个月唯一的jobcard数量，替代原来的SUM(CASE...)，避免重复计数。
• NoShows/Deleted：使用COUNT(DISTINCT CASE...)，仅统计符合状态条件的唯一jobcard，即使一个jobcard对应多条关联记录，也只算一次。
• Services/Repairs：同样用COUNT(DISTINCT CASE...)，确保只要jobcard下有一个符合条件的division，就只统计一次该jobcard，不会因为多个匹配的division重复计数。

额外优化建议 #

如果job表中jobcard和job_definition的组合确实有重复，你可以把关联子查询改成SELECT DISTINCT jobcard, job_definition FROM job，这样提前去重，减少关联后的数据量，提升查询效率：

INNER JOIN (SELECT DISTINCT jobcard, job_definition FROM job) jb 
  ON j.`id` = jb.`jobcard`

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