提问人：softboxkid 提问时间：11/16/2023 最后编辑：softboxkid 更新时间：11/16/2023 访问量：32
MySQL组未按预期生成
MySQL Group by not produce as expected
问：
我有几行记录需要计算每个月的交易数量。然后提出了以下查询，该查询计算每个月的 txn
输出：
我尝试进行单行输出。但是当我按 uid 分组时，它会求和并放入JAN2023列（这是不正确的）：
SELECT
(IF(payment.paymentMonth='01', COUNT(payment.paymentId), 0)) as JAN2023,
(IF(payment.paymentMonth='02', COUNT(payment.paymentId), 0)) as FEB2023,
(IF(payment.paymentMonth='03', COUNT(payment.paymentId), 0)) as MAC2023,
(IF(payment.paymentMonth='04', COUNT(payment.paymentId), 0)) as APR2023,
(IF(payment.paymentMonth='05', COUNT(payment.paymentId), 0)) as MAY2023,
(IF(payment.paymentMonth='06', COUNT(payment.paymentId), 0)) as JUN2023,
(IF(payment.paymentMonth='07', COUNT(payment.paymentId), 0)) as JUL2023,
(IF(payment.paymentMonth='08', COUNT(payment.paymentId), 0)) as AUG2023,
(IF(payment.paymentMonth='09', COUNT(payment.paymentId), 0)) as SEP2023,
(IF(payment.paymentMonth='10', COUNT(payment.paymentId), 0)) as OCT2023,
(IF(payment.paymentMonth='11', COUNT(payment.paymentId), 0)) as NOV2023,
(IF(payment.paymentMonth='12', COUNT(payment.paymentId), 0)) as DEC2023,
bill.uid as uid,
payment.paymentYear as paymentYear
FROM payment
JOIN bill ON bill.billId = payment.paymentBillId
AND paymentYear='2023'
AND (paymentSubChannel='epay' OR paymentSubChannel='gpay' OR paymentSubChannel='tpay' OR paymentSubChannel='bpay')
AND payment.paymentStatus = 1
AND bill.uid = 51
GROUP BY payment.paymentMonth
答：
2赞
Adrian Maxwell
11/16/2023
#1
您需要将条件逻辑放在聚合函数中。通常，这使用如下所示的案例表达式。
SELECT
COUNT(CASE WHEN payment.paymentMonth = '01' THEN 1 END) AS JAN2023
, COUNT(CASE WHEN payment.paymentMonth = '02' THEN 1 END) AS FEB2023
, COUNT(CASE WHEN payment.paymentMonth = '03' THEN 1 END) AS MAR2023
, COUNT(CASE WHEN payment.paymentMonth = '04' THEN 1 END) AS APR2023
, COUNT(CASE WHEN payment.paymentMonth = '05' THEN 1 END) AS MAY2023
, COUNT(CASE WHEN payment.paymentMonth = '06' THEN 1 END) AS JUN2023
, COUNT(CASE WHEN payment.paymentMonth = '07' THEN 1 END) AS JUL2023
, COUNT(CASE WHEN payment.paymentMonth = '08' THEN 1 END) AS AUG2023
, COUNT(CASE WHEN payment.paymentMonth = '09' THEN 1 END) AS SEP2023
, COUNT(CASE WHEN payment.paymentMonth = '10' THEN 1 END) AS OCT2023
, COUNT(CASE WHEN payment.paymentMonth = '11' THEN 1 END) AS NOV2023
, COUNT(CASE WHEN payment.paymentMonth = '12' THEN 1 END) AS DEC2023
, bill.uid as uid
, payment.paymentYear AS paymentYear
FROM payment
JOIN bill ON bill.billId = payment.paymentBillId
WHERE paymentYear = '2023'
AND (
paymentSubChannel = 'epay'
OR paymentSubChannel = 'gpay'
OR paymentSubChannel = 'tpay'
OR paymentSubChannel = 'bpay'
)
AND payment.paymentStatus = 1
AND bill.uid = 51
GROUP BY
paymentYear
, bill.uid
请记住，case 表达式只是确定特定月份是 1 还是 null，然后这些 1 将通过聚合进行计数。该技术称为“条件聚合”，即先条件，再聚合
评论
0赞
softboxkid
11/16/2023
感谢您对条件聚合的回答和简短解释。这个答案帮助我产生预期的结果，并希望能帮助他人。TQ型
评论