MYSQL 分组依据和联接 : incompatible with sql_mode=only_full_group_by

Question

我有两张 table 。发票表:

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `invoice_number` int(11) NOT NULL,
  `created_date` date NOT NULL,
  `expiry_date` date NOT NULL,
  `client_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和发票服务:

CREATE TABLE `invoice_services` (
  `id` int(11) NOT NULL,
  `invoice_id` varchar(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `qty` int(11) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我想查看每年每个月的利润:

SELECT DATE_FORMAT(i.created_date,'%Y-%m') as month, sum(profit) as profit
FROM invoices as i
JOIN (SELECT invoice_id, SUM(value) as profit FROM invoice_services isrv) isrv
ON i.id = isrv.invoice_id
GROUP BY month

但我收到错误:

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'isrv.invoice_id'; this is incompatible with sql_mode=only_full_group_by

我想保留这个 sql 模式，但我就是无法理解它。如有任何帮助，我们将不胜感激。

Answer 1

结果中不需要发票 ID，因此请将它们从 SELECT 列表中删除。

您需要将GROUP BY添加到子查询中。

SELECT DATE_FORMAT(i.created_date,'%Y-%m') as month, sum(profit) as profit
FROM invoices as i
JOIN (
    SELECT invoice_id, SUM(value) as profit 
    FROM invoice_services
    GROUP BY invoice_id) isrv
ON i.id = isrv.invoice_id
GROUP BY month