php - MySQL SUM() 给出的总数不正确

Question

我正在开发一个 php/mysql 数据库。我有两个表——事务和行动。在其他字段中，事项表包含“matterid”、“fixedfee”和“fee”。固定费用为 Y 或 N，费用可以是任意数字。

对于任何事情都可以采取多种行动。 actions 表包含“actionid”“matterid”“advicetime”“advicefee”。 advicetime 是建议持续的时间(以十进制格式)，advicefee 是一个数字。因此，为了计算某件事的建议成本，我使用 SUM(advicetime*advicefee)。

我想要做的是当 'fixedfee'=Y 时将所有 'fee' 值相加，以及所有这些事项的所有 SUM(advicetime*advicefee) 值的总和。

我尝试过使用:

SELECT
        SUM(matters.fee) AS totfixed,
        SUM(advicetime*advicefee) AS totbills,
    FROM matters
    INNER JOIN actions
        ON matters.matterid = actions.matterid
    WHERE fixedfee = 'Y'

但这不起作用，因为(我认为)它会在每次发生操作时将 issues.fee 相加。我也尝试过制作

SUM(DISTINCT matters.fee) AS totfixed 

但这不起作用，因为我认为它似乎遗漏了任何相同的费用(并且有几个事项具有相同的固定费用)。

我对此还很陌生，因此非常欢迎任何帮助。

Answer 1

but this doesn't work as (I think) it is adding up the matters.fee for every time there is an action. I have also tried making it ...

您遇到聚合扇出问题。只要选择查询中的主表的行数少于它所连接到的辅助表的行数，就会发生这种情况。连接会产生重复的行。因此，当应用聚合函数时，它们会作用于额外的行。

这里的主表是指应用聚合函数的表。在您的示例中，
* SUM(matters.fee) >> 表 matters 上的聚合。
* SUM(advicetime*advicefee) >> 表 actions
的聚合* fixedfee='Y' >> 表上的条件很重要

为了避免扇出问题:
* 始终将聚合应用于联接中最细粒度的表。
* 除非两个表具有一对一的关系，否则不要对两个表的字段应用聚合函数。
* 通过不同的子查询分别获取聚合，然后合并结果。这可以在 SQL 语句中完成，也可以导出数据然后再执行。

查询1:

SELECT SUM(fee) AS totfixed 
FROM matters 
WHERE fixedfee='Y'

查询2:

SELECT SUM(actions.advicetime*actions.advicefee) AS totbills 
FROM matters  
JOIN actions ON matters.matterid = actions.matterid 
WHERE matters.fixedfee = 'Y'

查询 1 和 查询 2 不会受到扇出的影响。此时您可以将它们都导出并在 php 中处理结果。或者您可以在 SQL 中组合它们:

SELECT query_2.totbills, query_1.totfixed
FROM (SELECT SUM(fee) AS totfixed 
    FROM matters 
    WHERE fixedfee='Y') query_1,

    (SELECT SUM(actions.advicetime*actions.advicefee) AS totbills 
    FROM matters  
    JOIN actions ON matters.matterid = actions.matterid 
    WHERE matters.fixedfee = 'Y') query_2

最后，SUM 不采用关键字DISTINCT。 DISTINCT 仅适用于 COUNT 和 GROUP_CONCAT 聚合函数。下面是一段无效的SQL

SUM(DISTINCT matters.fee) AS totfixed