為何SQL執行後，庫存.銷貨.進貨數額不對?
DROP DATABASE IF EXISTS db20220510;
#purchase sales stock 進貨 銷貨 存貨
CREATE DATABASE db20220510 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db20220510;
#CREATE TABLE 進貨明細(
CREATE TABLE productin_detail(
進貨單編號 VARCHAR(4) NOT NULL,
貨品編號 VARCHAR(4) NOT NULL,
進貨數量 INTEGER,
PRIMARY KEY (進貨單編號, 貨品編號)
);
CREATE TABLE orders_detail(
訂單編號 VARCHAR(4) NOT NULL,
產品編號 VARCHAR(4) NOT NULL,
購買數量 INTEGER,
PRIMARY KEY (訂單編號, 產品編號)
);
CREATE TABLE products(
產品編號 VARCHAR(4) NOT NULL ,
產品名稱 VARCHAR(20),
單位 VARCHAR(2),
單價 FLOAT,
供應商編號 VARCHAR(4),
PRIMARY KEY (產品編號)
);
INSERT INTO productin_detail VALUES ('I001','P001',100);
INSERT INTO productin_detail VALUES ('I001','P002',200);
INSERT INTO productin_detail VALUES ('I001','P003',100);
INSERT INTO productin_detail VALUES ('I001','P004',100);
INSERT INTO productin_detail VALUES ('I001','P005',100);
INSERT INTO productin_detail VALUES ('I002','P002',100);
INSERT INTO productin_detail VALUES ('I003','P003',300);
INSERT INTO productin_detail VALUES ('I003','P004',300);
INSERT INTO productin_detail VALUES ('I003','P005',330);
INSERT INTO productin_detail VALUES ('I004','P004',300);
INSERT INTO productin_detail VALUES ('I005','P005',400);
INSERT INTO productin_detail VALUES ('I006','P006',500);
INSERT INTO productin_detail VALUES ('I007','P007',200);
INSERT INTO productin_detail VALUES ('I007','P008',210);
INSERT INTO productin_detail VALUES ('I007','P009',220);
INSERT INTO productin_detail VALUES ('I007','P004',230);
INSERT INTO productin_detail VALUES ('I007','P003',240);
INSERT INTO productin_detail VALUES ('I008','P008',100);
INSERT INTO productin_detail VALUES ('I009','P009',100);
INSERT INTO productin_detail VALUES ('I010','P001',200);
INSERT INTO productin_detail VALUES ('I010','P002',120);
INSERT INTO productin_detail VALUES ('I010','P003',112);
INSERT INTO productin_detail VALUES ('I010','P010',280);
INSERT INTO orders_detail VALUES ('O001','P001',3);
INSERT INTO orders_detail VALUES ('O001','P002',5);
INSERT INTO orders_detail VALUES ('O001','P003',7);
INSERT INTO orders_detail VALUES ('O001','P004',8);
INSERT INTO orders_detail VALUES ('O001','P005',10);
INSERT INTO orders_detail VALUES ('O002','P002',1);
INSERT INTO orders_detail VALUES ('O003','P003',3);
INSERT INTO orders_detail VALUES ('O003','P004',30);
INSERT INTO orders_detail VALUES ('O003','P005',33);
INSERT INTO orders_detail VALUES ('O004','P004',3);
INSERT INTO orders_detail VALUES ('O005','P005',4);
INSERT INTO orders_detail VALUES ('O006','P006',5);
INSERT INTO orders_detail VALUES ('O007','P007',20);
INSERT INTO orders_detail VALUES ('O007','P008',21);
INSERT INTO orders_detail VALUES ('O007','P009',22);
INSERT INTO orders_detail VALUES ('O007','P004',23);
INSERT INTO orders_detail VALUES ('O007','P003',24);
INSERT INTO orders_detail VALUES ('O008','P008',1);
INSERT INTO orders_detail VALUES ('O009','P009',1);
INSERT INTO orders_detail VALUES ('O010','P001',20);
INSERT INTO orders_detail VALUES ('O010','P002',12);
INSERT INTO orders_detail VALUES ('O010','P003',112);
INSERT INTO orders_detail VALUES ('O010','P010',28);
INSERT INTO products VALUES ('P001','個人電腦','台',30000.0,'S001');
INSERT INTO products VALUES ('P002','筆記型電腦','台',50000.0,'S002');
INSERT INTO products VALUES ('P003','平板電腦','台',18000.0,'S003');
INSERT INTO products VALUES ('P004','iPhone 8手機','支',25000.0,'S004');
INSERT INTO products VALUES ('P005','iPhone 8 plus手機','支',26000.0,'S005');
INSERT INTO products VALUES ('P006','iPhone X手機','支',50000.0,'S006');
INSERT INTO products VALUES ('P007','雷射印表機','台',22000.0,'S007');
INSERT INTO products VALUES ('P008','彩色雷射印表機','台',40000.0,'S008');
INSERT INTO products VALUES ('P009','投影機','台',80000.0,'S009');
INSERT INTO products VALUES ('P010','26吋液晶螢幕','台',15000.0,'S010');
#計算庫存所需要用到的VIEW
DROP VIEW IF EXISTS countinventory_view;
CREATE VIEW countinventory_view AS
SELECT products.產品編號, products.產品名稱,SUM(productin_detail.進貨數量) AS 進貨合計,SUM(orders_detail.購買數量) AS 銷貨合計,
(sum(productin_detail.進貨數量)-sum(orders_detail.購買數量))AS 庫存合計
FROM db20220510.products,db20220510.productin_detail,db20220510.orders_detail
WHERE(db20220510.productin_detail.貨品編號=db20220510.orders_detail.產品編號)
AND(db20220510.products.產品編號=db20220510.productin_detail.貨品編號)
GROUP BY db20220510.products.產品編號;
SELECT * FROM countinventory_view;