mysql - Group By 无法计算 MySql 中剩余的数量

Question

我正在尝试计算交易完成后剩余的库存量，但它返回给我一个负数。我的 GROUP BY 可能做错了什么，因为它似乎没有工作，或者它是实际的计算（如你所知，我是一个完整的初学者）？

    CREATE TABLE items (
    amount int,
    price_for_each int,
    name VARCHAR(20),
    CONSTRAINT PK_items PRIMARY KEY (name)
);
INSERT INTO items (price_for_each, amount, name) VALUES 
(110,22,'2lof milk'),
(99,30,'6 cans of lemonade'),
(150,20,'Pack of butter'),
(450,13,'Roast chicken'),
(99,30,'Pack of rice'),
(20,50,'Banana'),
(200,30,'3kg sugar'),
(150,15,'Toast bread'),
(150,18,'Earl Grey tea');

CREATE TABLE itemsintransactions (
    name VARCHAR(20),
    t_id int,
    amount int,
    FOREIGN KEY (name) REFERENCES items(name),
    FOREIGN KEY (t_id) REFERENCES transactions(t_id)
);
INSERT INTO itemsintransactions (name, t_id, amount) VALUES
('6 cans of lemonade',1,1),
('Roast chicken',1,1),
('Pack of butter',1,1),
('Toast bread',1,1),
('2lof milk',1,2),
('Banana',1,3),
('3kg sugar',1,1),
('6 cans of lemonade',2,5),
('Pack of rice',2,1),
('6 cans of lemonade',3,3),
('Roast chicken',3,2),
('Pack of rice',3,1),
('Pack of butter',3,1),
('2lof milk',4,5),
('Banana',4,20),
('3kg sugar',4,8),
('6 cans of lemonade',5,10),
('Roast chicken',5,10),
('Pack of rice',5,10),
('Pack of butter',5,10),
('Toast bread',5,10),
('2lof milk',5,10),
('Banana',5,10),
('3kg sugar',5,10),
('Earl Grey tea',5,10);

CREATE VIEW ItemsLeft AS
SELECT items.name, (items.amount-sum(itemsintransactions.amount)) AS amount_left
FROM items, itemsintransactions
GROUP BY items.name
ORDER BY items.name;

标签： mysqlsqljoin

Answer 1

先聚合itemsintransactions，然后LEFT加入items结果：

SELECT i.name, i.amount - COALESCE(t.amount, 0) AS amount_left
FROM items AS i 
LEFT JOIN (
  SELECT name, SUM(amount) AS amount
  FROM itemsintransactions 
  GROUP BY name
) AS t ON t.name = i.name
ORDER BY i.name

请参阅演示。
结果：

> name               | amount_left
> :----------------- | ----------:
> 2lof milk          |           5
> 3kg sugar          |          11
> 6 cans of lemonade |          11
> Banana             |          17
> Earl Grey tea      |           8
> Pack of butter     |           8
> Pack of rice       |          18
> Roast chicken      |           0
> Toast bread        |           4