錯誤示例

SELECT
COUNT(*) AS count, p.type, d.active_date
FROM
device d
LEFT JOIN product p ON d.product_id = p.pid
WHERE
MONTH (active_date) = MONTH (CURRENT_DATE - INTERVAL 1 MONTH)
AND YEAR (active_date) = YEAR (CURRENT_DATE - INTERVAL 1 MONTH)
group by p.type
ORDER BY p.type, d.active_date

錯誤原因

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘d.active_date’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

正確示例

SELECT
COUNT(*) AS count, p.type, DATE_FORMAT(active_date, '%Y-%m') date
FROM
device d
LEFT JOIN product p ON d.product_id = p.pid
WHERE
MONTH (active_date) = MONTH (CURRENT_DATE - INTERVAL 1 MONTH)
AND YEAR (active_date) = YEAR (CURRENT_DATE - INTERVAL 1 MONTH)
group by p.type, date
ORDER BY p.type, date