mysql - only_full_group_by 不让我按我的意愿显示表格

Question

我有一个带有 MySQL 服务器版本 5.7.27 的新 VPS 服务器。在这个新的 VPS 中，我上传了一个数据库和一些代码，这些代码是我从以前的服务器上获得的，该服务器具有较旧的 MySQL 服务器版本。

MySQL 服务器版本 5.7.27 默认启用 only_full_group_by。当在新的 VPS 服务器中出现问题时，我试图运行一个包含 GROUP BY 语句的查询，该语句曾经在我的旧服务器中工作，但现在在新服务器中我收到以下消息。

1055:SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“ihrwpmmy_loyal.i.id”，它在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

我知道有删除/禁用 only_full_group_by 的选项，但我想正确地做事并更正我的查询。

我一直在阅读并且了解到将 i.id 添加到 GROUP BY 语句将再次显示表中的值，但我真的不想 GROUP BY i.id 相反，我只想 GROUP BY i.loadNumber 但我被迫 GROUP BY i.id 以消除错误。我真的也需要 GROUP BY i.loadNumber 因为我不想在表格上显示重复的负载号。

SELECT i.id AS ivoiceIDAOC,
        i.debtorId,
        i.loadNumber,
        i.amountUsd,
        i.sentDate,
        i.commissionStatus,
        i.paidDate,
        i.status,
        cp.amountUsd AS amountUsdCarriersPayments,
        i.amountUsd - SUM(cp.amountUsd) AS companyProfitUsd,
        ROUND((i.amountUsd - SUM(cp.amountUsd)) * 0.10, 2) AS salesRepProfitUsd,
        ROUND(((i.amountUsd - SUM(cp.amountUsd)) * 0.10) * 0.50, 2)  AS advanceOnCommissionUsd,
        l.id,
        l.clientName,
        l.repID
        FROM invoice i
        LEFT OUTER JOIN leads l ON i.debtorId = l.id
        INNER JOIN carriersPayments cp ON cp.loadNumber LIKE CONCAT('', i.loadNumber, '%')
        WHERE True
        ".$filter_AOC_date."
        ".$filter_AOC_salesRep."
        AND i.commissionStatus IS NULL
        GROUP BY
        i.id,
        i.loadNumber,
        cp.amountUsd
        ORDER BY i.id desc";

表格是这样的。

Load #|Customer Name| Paid Date |Revenue| Cost |Co Profit|SR Profit | AOC  |
------+-------------+-----------+-------+------+--------------------+------+
1570  | Customer 1  | 2019-09-19| $100  |  $50 |   $50   |   $5     | $2.5 |
1565  | Customer 2  | 2019-09-17| $102  |  $50 |   $52   |   $5.2   | $2.6 |
1565  | Customer 2  | 2019-09-16| $104  |  $50 |   $54   |   $5.4   | $2.7 |
1566  | Customer 3  | 2019-09-13| $106  |  $50 |   $56   |   $5.6   | $2.8 |

如您在示例表中所见，如果我被迫将 i.id 添加到我的 GROUP BY 语句中，我将获得两次加载编号 1565。
我只想看到不同的负载数。

Load #|Customer Name| Paid Date |Revenue| Cost |Co Profit|SR Profit | AOC  |
------+-------------+-----------+-------+------+--------------------+------+
1570  | Customer 1  | 2019-09-19| $100  |  $50 |   $50   |   $5     | $2.5 |
1565  | Customer 2  | 2019-09-17| $206  | $100 |   $106  |   $10.6  | $5.3 |
1566  | Customer 3  | 2019-09-13| $106  |  $50 |   $56   |   $5.6   | $2.8 |

Answer 1

你在select select some column not involbed in aggreagation function and not mentioned in group by this is not allowed by default for the most recent version of mysql

此外，您还在选择和聚合函数中使用了同一列，这很奇怪，您不能同时使用其中一个，似乎没有意义

根据您的代码，您可以使用适当的子查询重构您的查询

select loadNumber, SUM(amountUsd) sum_usd, SUM(amountCad) sum_cad
from  carriersPayments
group by loadNumber 

例子:

"SELECT i.id AS ivoiceIDAOC,
        i.debtorId,
        i.loadNumber,
        i.amountUsd,
        i.amountCad,
        i.sentDate,
        i.commissionStatus,
        i.paidDate,
        i.status,
        cp.amountUsd AS amountUsdCarriersPayments,
        cp.amountCad AS amountCadCarriersPayments,

        i.amountUsd - sum_usd AS companyProfitUsd,
        i.amountCad - sum_cad AS companyProfitCad,

        ROUND((i.amountUsd - sum_usd) * 0.10, 2) AS salesRepProfitUsd,
        ROUND((i.amountCad - sum_cad) * 0.10, 2) AS salesRepProfitCad,

        ROUND(((i.amountUsd - sum_usd) * 0.10) * 0.50, 2)  AS advanceOnCommissionUsd,
        ROUND(((i.amountCad - sum_cad) * 0.10) * 0.50, 2)  AS advanceOnCommissionCad,

        l.id,
        l.clientName,
        l.repID
        FROM invoice i
        INNER JOIN carriersPayments cp ON cp.loadNumber LIKE CONCAT('', i.loadNumber, '%')
        INNER JOIN  (
          select loadNumber, SUM(amountUsd) sum_usd, SUM(amountCad) sum_cad
          from  carriersPayments
          group by loadNumber 
          ) t ON t.loadNumber LIKE CONCAT('', i.loadNumber, '%')
        LEFT OUTER JOIN leads l ON i.debtorId = l.id
        WHERE True
        ".$filter_AOC_date."
        ".$filter_AOC_salesRep."
        AND i.commissionStatus IS NULL
        ORDER BY i.id desc";    

最后你不应该在 sql 中使用 php var 这有 sqlinject 的风险..为了避免这种情况你应该看看你的 db 驱动程序准备好的 statememnt 和绑定(bind)参数。