MySQL GROUP BY结果不符合预期：按月统计交易数遇格式问题

原查询按月分组时能正确输出每月数据，但改为按UID单行输出时统计值错误，核心问题是分组逻辑错误和聚合函数用法不当，修正后的查询如下：

SELECT 
  SUM(IF(payment.paymentMonth='01', 1, 0)) as JAN2023,
  SUM(IF(payment.paymentMonth='02', 1, 0)) as FEB2023,
  SUM(IF(payment.paymentMonth='03', 1, 0)) as MAR2023, -- 原MAC2023为拼写错误，修正为标准缩写MAR2023
  SUM(IF(payment.paymentMonth='04', 1, 0)) as APR2023,
  SUM(IF(payment.paymentMonth='05', 1, 0)) as MAY2023,
  SUM(IF(payment.paymentMonth='06', 1, 0)) as JUN2023,
  SUM(IF(payment.paymentMonth='07', 1, 0)))) as JUL2023,
  SUM(IF(payment.paymentMonth='08', 1, 0)) as AUG2023,
  SUM(IF(payment.paymentMonth='09', 1, 0)) as SEP2023,
  SUM(IF(payment.paymentMonth='10', 1, 0)) as OCT2023,
  SUM(IF(payment.paymentMonth='11', 1, 0)) as NOV2023,
  SUM(IF(payment.paymentMonth='12', 1, 0)) as DEC2023,
  bill.uid as uid,
  payment.paymentYear as paymentYear
FROM payment
JOIN bill ON bill.billId = payment.paymentBillId
WHERE paymentYear='2023'
  AND paymentSubChannel IN ('epay', 'gpay', 'tpay', 'bpay') -- 用IN简化多OR条件
  AND payment.paymentStatus = 1
  AND bill.uid = 51
GROUP BY bill.uid, payment.paymentYear -- 按UID和年份分组，确保单用户单行输出全年数据

关键修改说明： #

• 分组逻辑调整：从按月分组改为按uid和paymentYear分组，保证每个用户对应一行全年统计结果
• 聚合函数修正：用SUM(IF(...,1,0))替代原IF(...,COUNT(...),0)——COUNT()会统计分组内所有符合条件的记录数，而SUM()仅累加对应月份的交易次数，避免错误累加
• 条件简化：用IN替代多个重复OR，让语句更简洁易读
• 拼写修正：将三月的缩写从MAC2023改为行业标准的MAR2023

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