gpt4 book ai didi

mysql - 从数据库中获取所有订单 - 只返回一行

转载 作者:行者123 更新时间:2023-11-29 03:20:14 25 4
gpt4 key购买 nike

我试图让表格链接在一起以返回电子邮件、订单号、日期、总成本和退回的项目数量,但在我希望返回的两行数据中,只有一个是。表格如下;

Table: Customer
+------------+---------------------+-----------------+
| CustomerID | EmailAddress | DeliveryAddress |
+------------+---------------------+-----------------+
| BLF1 | FredBloggs@NT.co.uk | NULL |
| SMJ2 | JoeSmith@NT.co.uk | NULL |
+------------+---------------------+-----------------+


Table: Item
+----------+-------------+-----------+
| ItemCode | Description | UnitPrice |
+----------+-------------+-----------+
| 1234 | Ring Binder | 1.5 |
| 3456 | Stapler | 2.99 |
| 8967 | Divider | 0.5 |
| 9684 | Scissors | 1.99 |
+----------+-------------+-----------+


Table: ItemOrder
+-------------+----------+---------------+
| OrderNumber | ItemCode | OrderQuantity |
+-------------+----------+---------------+
| 012367 | 1234 | 3 |
| 012367 | 3456 | 1 |
| 012367 | 8967 | 4 |
| 034231 | 3456 | 4 |
| 034231 | 9684 | 2 |
+-------------+----------+---------------+


Table: OnlineOrder
+-------------+------------+------------+
| OrderNumber | CustomerID | OrderDate |
+-------------+------------+------------+
| 012367 | BLF1 | 2009-05-01 |
| 034231 | SMJ2 | 2009-05-03 |
+-------------+------------+------------+

我运行的查询是:

SELECT Customer.EmailAddress, 
OnlineOrder.OrderNumber,
OnlineOrder.OrderDate,
Count(Item.ItemCode) as TotalItems,
FORMAT(SUM(Item.UnitPrice*ItemOrder.OrderQuantity), 2) as TotalPrice
FROM Customer,
Item,
OnlineOrder,
ItemOrder
WHERE OnlineOrder.OrderNumber = ItemOrder.OrderNumber
AND Customer.CustomerID = OnlineOrder.CustomerID
AND Item.ItemCode = ItemOrder.ItemCode;`

这只返回一条记录;

+---------------------+-------------+------------+------------+------------+
| EmailAddress | OrderNumber | OrderDate | TotalItems | TotalPrice |
+---------------------+-------------+------------+------------+------------+
| FredBloggs@NT.co.uk | 012367 | 2009-05-01 | 5 | 25.43 |
+---------------------+-------------+------------+------------+------------+

为什么查询不返回第二个订单,以及该订单的详细信息和购买者?

最佳答案

这是使用正确的 join/on 子句格式化的相同查询。我还更改了每个表的别名引用。特别是对于较大的表名,它可以显着简化可读性。

SELECT 
C.EmailAddress,
OO.OrderNumber,
OO.OrderDate,
Count(I.ItemCode) as TotalItems,
FORMAT(SUM(I.UnitPrice * IO.OrderQuantity), 2) as TotalPrice
FROM
OnlineOrder OO
JOIN ItemOrder IO
ON OO.OrderNumber = IO.OrderNumber
JOIN Item I
ON IO.ItemCode = I.ItemCode
JOIN Customer C
ON OO.CustomerID = C.CustomerID
GROUP BY
OO.OrderNumber

我唯一添加的是 GROUP BY 子句。由于您希望按订单计算总计,因此按订单分组。如果您希望每个客户的总数,您需要删除明确的订单号和日期以查看所有订单项和所有订单项总计。

关于mysql - 从数据库中获取所有订单 - 只返回一行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46473355/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com