【代码】Mysql 查询近一个月各类型设备新增数量

本文主要是介绍【代码】Mysql 查询近一个月各类型设备新增数量，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

错误示例

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				

这篇关于【代码】Mysql 查询近一个月各类型设备新增数量的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---