我在計算我的測驗零售店資料庫中商品庫存的價值時遇到問題。這是表的 DDL
create table itemtype
(
itemtypeid char(5),
itemtypename varchar(255) not null,
constraint pk_itemid primary key(itemtypeid),
constraint c_itemtypeid check(itemtypeid regexp '^IT[0-9]{3}$')
);
create table item
(
itemid char(5),
itemname varchar(255) not null,
price int not null,
itemtypeid char(5),
constraint pk_itemid primary key(itemid),
constraint fk_itemtype foreign key(itemtypeid) references itemtype(itemtypeid) on delete cascade,
constraint c_itemid check(itemid regexp '^IM[0-9]{3}$')
);
create table supplier
(
supplierid char(5),
suppliername varchar(255),
supplierphone char(12),
constraint pk_supplierid primary key(supplierid),
constraint c_supplierphone check(supplierphone regexp '[0-9]{12}'),
constraint c_supplierid check(supplierid regexp '^SP[0-9]{3}$'),
constraint u_supplierphone unique(supplierphone)
);
create table supplytransaction
(
transactionid char(5),
supplydate date not null,
supplierid char(5) not null,
constraint pk_transactionid primary key(transactionid),
constraint fk_supply_supplierid foreign key(supplierid) references supplier(supplierid),
constraint c_transid check(transactionid regexp '^TR[0-9]{3}$')
);
create table purchase
(
purchaseid char(5),
purchasedate date not null,
constraint pk_purchaseid primary key(purchaseid),
constraint c_purchaseid check(purchaseid regexp '^PU[0-9]{3}$')
);
create table detailpurchase
(
purchaseid char(5),
itemid char(5),
purchasequantity int not null,
purchaseprice int not null,
constraint pk_item_purchase primary key(purchaseid,itemid),
constraint c_pqty check(purchasequantity >0),
constraint fk_purchaseid foreign key(purchaseid) references purchase(purchaseid) on delete cascade,
constraint fk_purchase_itemid foreign key(itemid) references item(itemid) on delete cascade
);
create table detailsupplytransaction
(
transactionid char(5),
itemid char(5),
supplyquantity int not null,
price int not null,
constraint pk_item_supply primary key(transactionid,itemid),
constraint c_sqty check(supplyquantity >0),
constraint fk_suply_purchaseid foreign key(transactionid) references supplytransaction(transactionid) on delete cascade,
constraint fk_supply_itemid foreign key(itemid) references item(itemid) on delete cascade
);
用于填充表的 DML
專案型別表
insert into itemtype values('IT001','Cheese');
insert into itemtype values('IT002','Beverage');
insert into itemtype values('IT003','Snack');
專案表
insert into item values('IM001','Fried seaweed',11000,'IT003');
insert into item values('IM002','Coca-Cola',20000,'IT002');
insert into item values('IM003','Blue Chiz',10000,'IT001');
供應商表
insert into supplier values('SP001','Bluechiz guy','624806854886');
insert into supplier values('SP002','chipsdude','023502902081');
insert into supplier values('SP003','Cocacola','387648707137');
供應交易表
insert into supplytransaction values('TR001','2021-01-01','SP001');
insert into supplytransaction values('TR002','2021-01-01','SP002');
insert into supplytransaction values('TR003','2021-01-01','SP003');
采購表
insert into purchase values('PU001','2021-12-01');
詳情采購表
INSERT INTO detailpurchase values('PU001','IM001',3,10000);
INSERT INTO detailpurchase values('PU001','IM002',1,20000);
INSERT INTO detailpurchase values('PU001','IM003',3,11000);
明細供應表
insert into detailsupplytransaction values('TR001','IM003',1000,2000);
insert into detailsupplytransaction values('TR002','IM002',150,2000);
insert into detailsupplytransaction values('TR003','IM001',100,2000);
然后我有這個查詢，它根據我們在 where 子句中輸入的 itemtypeid 獲取商品庫存的資料。
SELECT
itemname,sum(supplyquantity)-sum(purchasequantity) stock
FROM itemtype it
inner join item i on i.itemtypeid=it.itemtypeid
left join detailpurchase dp on dp.itemid=i.itemid
inner join detailsupplytransaction dst on dst.itemid=i.itemid
inner join supplytransaction st on st.transactionid=dst.transactionid
where itemtypename='Cheese'
group by itemname;
當我們執行上述查詢時，一切正常，預期結果為 997（1000（BlueChiz 股票）- 3（已購買））。
但是，當我們添加另一個 detailsupply 事務時，該值不正確。例如
insert into supplytransaction values('TR004','2021-01-01','SP001');
insert into detailsupplytransaction values('TR001','IM003',400,2000);
如果我們重新運行查詢，庫存是 1394（1400 - 6（在購買中我們可以看到該商品的購買數量是 3 而不是 6））
這是我的核心問題，我該如何解決這個問題？我知道一些解決方案是添加具有不同 PK 值的相同專案，但我認為這不是最佳的并且有點多余。那么還有其他方法可以解決這個問題嗎？
uj5u.com熱心網友回復：
對于新手查詢，您遇到一種非常常見的情況，稱為笛卡爾結果（或交叉應用）。讓我描述一下。當您根據給定鍵從不同的表中進行聚合時，并且該鍵 ID 在任一表中多次存在時，您將獲得它的乘積（乘法）。
因此，讓我們看一下單個專案的采購表。
Purchases
ItemID Item ItemQty
1 A 5
1 A 7
1 A 9
Sales
ItemID Item ItemQty
1 A 2
1 A 1
1 A 3
因此，因為您正在加入 ItemID（例如），所以您得到的是 - 對于每個購買專案，您將獲得每個 SALES 專案
Purchase 5 Sales 2
Purchase 5 Sales 1
Purchase 5 Sales 3
Purchase 7 Sales 2
Purchase 7 Sales 1
Purchase 7 Sales 3
Purchase 9 Sales 2
Purchase 9 Sales 1
Purchase 9 Sales 3
看到問題以及為什么看到糟糕的數學結果？在這些情況下，您應該根據給定的 ID 將購買和銷售單獨預聚合為它們自己的預聚合，因此連接的每一側只有一個記錄
sum( Purchase ) = 5 7 9 = 21
sum( Sales ) = 2 1 3 = 6
現在，你怎么寫它。
SELECT
itemname,
coalesce( st.SumOfSupply, 0 )
- coalesce( dp.SumOfPurchase, 0 ) stock
FROM
itemtype it
inner join item i
on it.itemtypeid = i.itemtypeid
left join
( select itemid, sum( purchasequantity ) SumOfPurchase
from detailpurchase
group by itemid ) dp
on i.itemid = dp.itemid
inner join
( select itemid, sum( supplyquantity ) SumOfSupply
from supplytransaction
group by itemid ) st
on dst.transactionid = st.transactionid
inner join
detailsupplytransaction dst
on i.itemid = dst.itemid
where
itemtypename = 'Cheese'
group by
itemname;
現在，您沒有提到您的詳細供應交易表，但我敢打賭，這也可能是一個可能造成聚合問題的問題，但留給您繼續跟進。更好地理解問題的原因并知道如何解決它，而不是總是依賴于詢問。詢問沒有問題，只是幫助建議您將來可能遇到的情況。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413602.html
標籤：