我正在努力获得最畅销的产品。表格
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)。如何做到这一点?
select 子句中有 3 列,它们不是聚合函数
i.id
、的一部分i.name
,oi.name
也不是 group by 子句的一部分。如果您选择非分组列或根据非分组列计算的值,则未定义返回值来自哪一行。如果使用ONLY_FULL_GROUP_BY SQL_MODE ,则不允许这样做,我强烈建议启用它。
i.id
您可以通过在、i.name
、列中使用任何聚合函数(例如 MIN()、MAX() 等)来修复查询,oi.name
或者在 group by 子句中添加这些列。在修复了一个额外的逗号和两个缺失列的数据示例后,查询运行良好。
请参阅此处的示例
拥有
name
和price
冗余存储是模式设计中的禁忌。从其中删除这些列order_items
并引用其他表以获取这些值。这应该可以解决这个GROUP BY
问题。