通过将位于两个独立表中的价格和数量列相乘，计算餐厅数据库中每个订单的总价

Question

我有两个表：Contain和FoodItem。它们的描述和内容如下所示

CREATE TABLE Customer (
CustomerID INT NOT NULL AUTO_INCREMENT,
Fname VARCHAR(10),
Lname VARCHAR(10), 
PRIMARY KEY (CustomerID)
);
CREATE TABLE Orders (
OrderNo INT NOT NULL AUTO_INCREMENT,
OrderDate DATETIME NOT NULL, 
CustomerID INT,
PRIMARY KEY (OrderNo),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
CREATE TABLE FoodInfo (
ItemName VARCHAR(10) NOT NULL,
Description VARCHAR(10),
PRIMARY KEY (ItemName)
);
CREATE TABLE FoodItem (
ItemName VARCHAR(10),
OrderNo INT,
Price SMALLINT,
PRIMARY KEY (ItemName, OrderNo),
FOREIGN KEY(OrderNo) REFERENCES Orders (OrderNo),
FOREIGN KEY(ItemName) REFERENCES FoodInfo (ItemName)
);
CREATE TABLE Contain(
OrderNo INT,
ItemName VARCHAR(10),
Quantity INT,
PRIMARY KEY(OrderNo, ItemName),
FOREIGN KEY(OrderNo) REFERENCES Orders (OrderNo),
FOREIGN KEY(ItemName) REFERENCES FoodInfo (ItemName)
);
INSERT INTO Customer VALUES
(1, Velda, Quigley),
(2, Abelardo, Stoltenber),
(3, Ludwig, Dare),
(4, Jordan, Beahan),
(5, Saul, Rohan),
(6, Una, Bergnaum),
(7, Richie, Bauch),
(8, Rubie, Gibson),
(9, Holden, Nader),
(10, Lance, Jacobi);

INSERT INTO Orders VALUES 
(2019-10-09 01:22:26, 1),
(1987-07-06 05:54:57, 2),
(1982-12-01 02:03:54, 3),
(2008-01-18 22:15:48, 9),
(2006-01-13 23:24:00, 2),
(1983-02-02 19:57:23, 5),
(1976-08-12 20:54:17, 4),
(1988-10-27 00:07:06, 8),
(2006-11-17 18:11:30, 5),
(2006-10-20 02:52:03, 10);
INSERT INTO FoodInfo VALUES
(assumenda, Architecto),
(iste, Totam est ),
(laborum, Maxime et ),
(neque, Est qui eu),
(quidem, Reiciendis),
(quos, Velit eum ),
(recusandae, Ea est fug),
(repudianda, Cumque aut),
(sit, Rem est te),
(sunt, Reiciendis);
INSERT INTO FoodItem VALUES
(assumenda, 1, 5),
(iste, 2, 10),
(laborum, 3, 20),
(neque, 1, 23),
(quidem, 2, 44),
(quos, 3, 51),
(recusandae, 7, 56),
(repudianda, 8, 71),
(sit, 9, 98),
(sunt, 10, 23);
INSERT INTO Contain VALUES 
(1, 'assumenda', 1),
(2, 'iste', 2),
(3, 'laborum', 3),
(1, 'neque', 1),
(2, 'quidem', 2),
(3, 'quos', 4),
(7, 'recusandae', 5),
(8, 'repudianda', 2),
(9, 'sit', 1),
(10, 'sunt', 1); 

我的目标是将与订单号相关的每个项目的数量和价格的值相乘，并将它们相加，得到产生的总金额。以下是我尝试过的：

SELECT Contain.OrderNo
, Contain.Quantity
, FoodItem.Price 
FROM Contain
, FoodItem 
WHERE Contain.OrderNo = FoodItem.OrderNo;
+---------+----------+-------+
| OrderNo | Quantity | Price |
+---------+----------+-------+
|       1 |        1 |     5 |
|       1 |        1 |    23 |
|       1 |        1 |     5 |
|       1 |        1 |    23 |
|       2 |        2 |    10 |
|       2 |        2 |    44 |
|       2 |        2 |    10 |
|       2 |        2 |    44 |
|       3 |        3 |    20 |
|       3 |        3 |    51 |
|       3 |        4 |    20 |
|       3 |        4 |    51 |
|       7 |        5 |    56 |
|       8 |        2 |    71 |
|       9 |        1 |    98 |
|      10 |        1 |    23 |
+---------+----------+-------+
16 rows in set (0.00 sec)

使用GROUP BY：

SELECT FoodItem.OrderNo
, SUM(FoodItem.Price * Contain.Quantity) AS TOTAL 
FROM FoodItem 
JOIN Contain 
ON FoodItem.OrderNo = Contain.OrderNo 
GROUP 
BY Contain.OrderNo;
+---------+-------+
| OrderNo | TOTAL |
+---------+-------+
|       1 |    56 |
|       2 |   216 |
|       3 |   264 |
|       7 |   280 |
|       8 |   142 |
|       9 |    98 |
|      10 |    23 |
+---------+-------+
7 rows in set (0.00 sec)

但是，我想要的输出是这样的：

所需输出：

+---------+-------+
| OrderNo | TOTAL |
+---------+-------+
|       1 |    28 |
|       2 |   108 |
|       3 |   264 |
|       7 |   280 |
|       8 |   142 |
|       9 |    98 |
|      10 |    23 |
+---------+-------+

我无法找出正确的SQL语句来生成此结果。有人能帮我吗？

Answer 1

您还需要在JOIN中包含ItemName：

SELECT fi.OrderNo, SUM(dfi.Price * c.Quantity) AS TOTAL 
FROM FoodItem fi JOIN
Contain c
ON fi.OrderNo = c.OrderNo AND
fi.ItemName = c.ItemName
GROUP BY c.OrderNo;

比赛不只是在一列，而是在两列。您需要两个行或不应该匹配的行都匹配，结果会相乘。

Answer 2

在联接中添加另一列ItemName以使其唯一，否则将获得交叉联接

SELECT 
FoodItem.OrderNo,
SUM(FoodItem.Price * Contain.Quantity) AS TOTAL
FROM
FoodItem
JOIN
Contain ON FoodItem.OrderNo = Contain.OrderNo AND FoodItem.`ItemName` = Contain.`ItemName`
GROUP BY Contain.OrderNo;

订单号|合计------：|----：1 | 282 | 1083 | 2647 | 2808 | 14298年9月9日10|23

db<gt；小提琴这里