mysql 更改SELECT语句中的DATE_FORMAT导致ORDER BY出现问题

hrysbysz  于 2023-04-19  发布在  Mysql
关注(0)|答案(1)|浏览(137)

我正在为大学做一个数据库作业,但我被卡住了。我已经完成了99%的任务。任务简介是:

创建查询以查看企业过去12个月的年销售额,显示每月销售的产品总数和销售的总价格(使用带汇总的group by)

SELECT date_format(_order.orderDate, '%M') AS 'Month',
       sum(orderItem.quantity) AS 'Total Items Purchased',
       sum(orderItem.unitPrice * orderItem.quantity) AS 'Total Price' 
FROM orderItem
LEFT JOIN _order ON orderItem.orderID = _order.orderID
WHERE orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY date_format(_order.orderDate, '%M') WITH ROLLUP
ORDER BY date_format(_order.orderDate, '%M');

我很确定这满足了简短的要求,即使月份是无序的,但它让我发疯。(我现在知道不要在我的数据库中使用关键字)是通常的MySQL格式,YYYY-MM-DD,所以我在SELECT语句中将它们改为月份,这样我就可以将数据汇总为每个月的总计。但是这个查询一直给我按字母顺序排列的月份列表。
我已经尝试了大量的建议,按日期排序,我已经在这里和其他网站上找到,但每次我改变GROUP BY部分,我得到error 1140关于它与sql_mode=only_full_group_by不兼容,每次我改变ORDER BY,我得到error 1055,这也是关于它与sql_mode=only_full_group_by不兼容。
我知道我可以禁用sql_mode=only_full_group_by,但我真的很想知道我做错了什么。学习很重要。
更多信息:这是我的“_order”和“orderItem”表。

CREATE TABLE IF NOT EXISTS _order(
   orderID VARCHAR(10), 
   customerID VARCHAR(10), 
   staffID VARCHAR(10), 
   orderDate DATE NOT NULL,
   deliveryDate DATE NOT NULL,
   deliveryStreet VARCHAR(50) NOT NULL,
   deliveryCounty VARCHAR(20) NOT NULL,
   deliveryEircode VARCHAR(8) NOT NULL,  
   orderStatus VARCHAR(50) NOT NULL,
   PRIMARY KEY (orderID)
);

CREATE TABLE IF NOT EXISTS orderItem(
   itemID VARCHAR(10), 
   orderID VARCHAR(10), 
   productID VARCHAR(10), 
   quantity INT NOT NULL, 
   unitPrice DECIMAL(12, 2) NOT NULL,
   PRIMARY KEY (itemID)
);

这些是我插入到“_order”和“orderItem”表中的数据示例。

INSERT INTO _order (orderID, customerID, staffID, orderDate, deliveryDate, deliveryStreet, deliveryCounty, deliveryEircode, orderStatus) VALUES ('SO65301830', 'SC45398810', 'SE55900726', '2023-05-28', '2023-05-30', '0 Aberg Parkway', 'Mayo', 'A08 OY80', 'Out for Delivery');

INSERT INTO orderItem (itemID, orderID, productID, quantity, unitPrice) VALUES ('OL127', 'SO65301830', 'SP07', 4, 14.99);

我想做的是把我所有订单中的所有数量和单价都加起来,然后把它们和它们的销售月份一起显示出来。上面的语句给了我:
| 月|采购项目总数|总销售额|
| --------------|--------------|--------------|
| 四月|五十四|6444.46|
| 八月|四十一|11709.59|
| 十二月|三十|10704.70|
等等
我想要的是:
| 月|采购项目总数|总销售额|
| --------------|--------------|--------------|
| 一月|二十八|6204.72|
| 二月|二十七|6909.73|
| 行军|四十七|13834.53|
等等
希望这篇文章能把事情弄清楚。对不起,这是我的第一篇文章。

bcs8qyzn

bcs8qyzn1#

您可以添加一个聚合到您的订单,以获得您想要的结果

SELECT date_format(_order.orderDate, '%M') AS 'Month',
       sum(orderItem.quantity) AS 'Total Items Purchased',
       sum(orderItem.unitPrice * orderItem.quantity) AS 'Total Price' 
FROM orderItem
LEFT JOIN _order ON orderItem.orderID = _order.orderID
WHERE orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY date_format(_order.orderDate, '%M') WITH ROLLUP
ORDER BY  MIN(DATE_FORMAT(_order.orderDate,'%Y%m'));
采购项目总数总价
五月五十九点九六
联系我们一百一十九点九二
八月五十九点九六

fiddle

相关问题