我正在努力获得最畅销的产品。表格
CREATE TABLE `items`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`price` double(8, 2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB
CREATE TABLE `order_items`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`item_id` bigint(20) unsigned DEFAULT NULL,
`order_set_menu_id` bigint(20) unsigned DEFAULT NULL,
`set_menu_item_id` bigint(20) unsigned DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`price` double(8, 2) DEFAULT NULL,
`qty` int(11) NOT NULL COMMENT 'item quantity',
PRIMARY KEY (`id`),
CONSTRAINT `order_items_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE SET NULL,
) ENGINE = InnoDB
以下是查询,
select `i`.`id`,
`i`.`name`,
oi.name as prevName,
SUM(oi.qty) as totalQty,
SUM(oi.price * oi.qty) as total,
COUNT(oi.order_id) as sales
from `items` as `i`
inner join `order_items` as `oi` on `oi`.`id` = `i`.`id`
where `oi`.`order_set_menu_id` is null
and year(`oi`.`created_at`) = 2023
and month(`oi`.`created_at`) = 08
group by `oi`.`item_id`
order by `sales` desc
错误是
SQLSTATE[42000]: Syntax error or access violation: 1055 'the_db.oi.name' isn't in GROUP BY (SQL: select `i`.`id`, `i`.`name`, oi.name as prevName, SUM(oi.qty) as totalQty, SUM(oi.price * oi.qty) as total, COUNT(oi.order_id) as sales from `items` as `i` inner join `order_items` as `oi` on `oi`.`id` = `i`.`id` where `oi`.`order_set_menu_id` is null and year(`oi`.`created_at`) = 2023 and month(`oi`.`created_at`) = 08 group by `oi`.`item_id`, `i`.`id`, `i`.`name` order by `sales` desc)
我可以解释一下为什么会出现错误吗?此查询也适用于数据库客户端(例如 tableplus),但不适用于应用程序(Laravel)。如何做到这一点?