SQL编写触发1055错误：窗口函数查询与only_full_group_by模式冲突

你遇到的1055报错由两个原因共同导致：

• 你的MySQL服务开启了sql_mode=only_full_group_by约束，该约束要求SELECT子句中的非聚合字段必须出现在GROUP BY子句中，否则无法通过语法校验。
• 你使用的MySQL版本低于8.0，不支持窗口函数语法，SQL解析器无法识别OVER()关键字，会将你写的SUM(...) OVER(...)识别为普通聚合函数，默认判定你需要执行全局聚合操作，要求vendor_id和第二个字段的单行计算结果都加入GROUP BY子句，因此触发报错。

根据你的使用场景可选择对应方案：

方案1：保留明细+全局/供应商维度汇总余额 #

如果你需要返回每笔有余额的发票明细，同时附带全局总余额、对应供应商总余额：

若可升级MySQL版本 #

升级到MySQL 8.0及以上版本，仅需给第二个计算列添加别名即可正常运行原逻辑：

SELECT 
  vendor_id, 
  invoice_total - payment_total - credit_total AS invoice_balance,
  SUM(invoice_total - payment_total - credit_total) OVER() AS total_balance, 
  SUM(invoice_total - payment_total - credit_total) OVER(PARTITION BY vendor_id) AS total_vendor_balance
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0;

若无法升级MySQL版本 #

使用子查询关联的方式实现相同逻辑，兼容MySQL 5.x版本：

SELECT 
  i.vendor_id,
  i.invoice_total - i.payment_total - i.credit_total AS invoice_balance,
  t.total_balance,
  v.total_vendor_balance
FROM invoices i
CROSS JOIN (
  SELECT SUM(invoice_total - payment_total - credit_total) AS total_balance
  FROM invoices
  WHERE invoice_total - payment_total - credit_total > 0
) t
INNER JOIN (
  SELECT 
    vendor_id,
    SUM(invoice_total - payment_total - credit_total) AS total_vendor_balance
  FROM invoices
  WHERE invoice_total - payment_total - credit_total > 0
  GROUP BY vendor_id
) v ON i.vendor_id = v.vendor_id
WHERE i.invoice_total - i.payment_total - i.credit_total > 0;

方案2：仅需供应商维度汇总余额 #

如果你不需要单发票明细，只需要统计每个供应商的总余额+全局总余额，直接用普通GROUP BY即可，兼容所有版本：

SELECT 
  vendor_id,
  SUM(invoice_total - payment_total - credit_total) AS total_vendor_balance,
  (SELECT SUM(invoice_total - payment_total - credit_total) FROM invoices WHERE invoice_total - payment_total - credit_total > 0) AS total_balance
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0
GROUP BY vendor_id;

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