我编写了一个查询，该查询将三个表连接到一个客户表，对各个字段的值进行计数，并返回按销售代表细分的销售和收入概览：
SELECT
u.id AS `userId`,
u.`username`,
(SELECT COUNT(*) FROM `SaleSet` s WHERE s.`pitchedBy_id` = `userId` AND s.setCompleteAt BETWEEN '2012-07-02 00:00:00' AND '2012-07-02 23:59:59') AS `transfers`,
COUNT(c.id) AS `closes`,
COUNT(IF(c.saleType_id = 1, 1, NULL)) AS `regS_sales`,
COUNT(IF(c.saleType_id = 2, 1, NULL)) AS `pd_sales`,
COUNT(IF(c.saleType_id = 4, 1, NULL)) AS `attempted_sales`,
COUNT(IF(c.CustomerStatus_id IN (5,6,9,16), 1,NULL)) AS `complete`,
COUNT(IF(c.CustomerStatus_id IN (8,18), 1,NULL)) AS `canceled`,
COUNT(IF(c.CustomerStatus_id IN (1,12,13), 1,NULL)) AS `pending`,
COUNT(IF(c.CustomerStatus_id = 20, 1,NULL)) AS `post_dated`,
SUM(IF(p.saleType_id = 2, p.`authOnlyAmount`,0)) AS `pdPotRev`,
#SUM(IF(c.saleType_id = 2 AND t.`captured` = 0, p.`authOnlyAmount`,0)) AS `pdCapRev`,
SUM(t.amount) AS `fwRevAuthed`,
SUM(IF(p.saleType_id = 2 AND t.`captured` = 0, t.amount,0)) AS `fwCaptured`
FROM customer c
LEFT JOIN `User` u ON u.id = c.`salesRep_id`
LEFT JOIN `Transaction` t
ON t.`customer_id` = c.`id`
AND t.transactionType = 'Auth'
LEFT JOIN `Purchase` p ON p.`customer_id` = c.`id`
#WHERE c.`salesRep_id` = 10
WHERE c.`activationDate` BETWEEN '2012-07-02 00:00:00' AND '2012-07-02 23:59:59'
GROUP BY u.`id`
为什么此列返回0而不是t.amount的总和：
SUM(IF(p.saleType_id = 2 AND t.captured = 0, t.amount,0)) AS fwCaptured？如您所见，我在上面两行做了完全一样的事情，并且工作正常。这是结果的示例：
userId username transfers closes regS_sales pd_sales attempted_sales complete canceled pending post_dated pdPotRev fwRevAuthed fwCaptured
10 doughaase 17 4 3 1 0 4 0 0 0 50.00 298.00 0.00
65 davidgarber 13 5 5 0 0 4 0 0 0 0.00 595.00 0.00
70 morgantaylor 5 2 2 0 0 0 2 0 0 0.00 198.00 0.00
76 shayans 8 1 0 1 0 1 0 0 0 99.00 99.00 0.00
96 regananson 5 3 3 0 0 3 0 0 0 0.00 248.00 0.00
pdPotRev以完全相同的方式使用完全相同的功能，并且运行良好。
最佳答案
也许每个捕获的类型2的销售的t.amount都是0，或者甚至可能是正值和负值也一样。
在上面的字段中，您要对另一个字段求和，因此它返回一个不同的值是有意义的。您甚至可以从完全不同的表中进行选择。
如有疑问，请删除总和和分组，然后选择值。您应该能够立即发现问题。
[编辑]
甚至都不是真的。 pdPotRev根本不使用p.captured字段。包含相同条件的行是pdCapRev，已将其注释掉。因此，很可能IF(p.saleType_id = 2 AND t.captured永远都不会评估为true。
关于mysql - 使用count和if的Left Joins和Aggregate函数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11587267/