我有三張桌子。
“部門”表：department_name，department_id。
表“LowPrice”：departmend_id、product_name、已購買、已售出、價格。
表“基本”：departmend_id、product_name、購買、出售、價格。
在“基本”和“低”表中，可以有多個記錄屬于一個部門。
這些表之間沒有關系。我想開發一個查詢，該查詢將按部門表中的部門編號對低表和基本表中的記錄進行分組。分組時，將每個部門的“基本”和“低價”表中的銷售和采購成本相加。
我的查詢代碼：
SELECT Departments.departmend_id,
Sum([Basic]![purchased] [Basic]![price]) AS PurchasedBasic
Sum([Basic]![sold] [Basic]![price]) AS SoldBasic,
Sum([Basic]![purchased] [Basic]![price]) - Sum([Basic]![sold][Basic]![price]) AS DiffBasic,
Sum([LowPrice]![purchased] [LowPrice]![price]) AS PurchasedLowPrice,
Sum([LowPrice]![sold] [LowPrice]![price]) AS SoldLowPrice,
Sum([LowPrice]![purchased] [LowPrice]![price]) - Sum([LowPrice]![sold] [LowPrice]![price]) AS DiffLowPrice
FROM (Departments LEFT JOIN Basic ON Departments.departmend_id = Basic.departmend_id) LEFT JOIN LowPrice ON Departments.departmend_id = LowPrice.departmend_id
GROUPBY Departments.departmend_id;
But now only those departments for which there are records in only one table are counted correctly. If there are records for a department in two tables, then their number is multiplied and the query gives an incorrect result.
Please enlighten me what I need to change in my query.
uj5u.com熱心網友回復：
考慮將LowPrice和BasicPrice合并到一張表中，因為它們定性地包含相同的資訊。簡單地，添加一個型別欄位來指定低價和基本價格記錄。
Make-Table 查詢 （運行一次）
SELECT p.*
INTO Prices
FROM
(SELECT b.department_id,
b.product_name,
b.purchased,
b.sold,
b.price,
'Basic Price' AS type
FROM BasicPrice b
UNION ALL
SELECT l.department_id,
l.product_name,
l.purchased,
l.sold,
l.price,
'Low Price' AS type
FROM LowPrice l
) p
一旦您正確規范了這兩個關系以獲得更有效的存盤，運行條件??聚合。下面使用 MS Access 支持的表別名和計算列。
條件聚合查詢
SELECT d.departmend_id,
d.department_name,
SUM(IIF(p.Type = 'Basic Price', p.[purchased] * p.[price], NULL)) AS PurchasedBasic,
SUM(IIF(p.Type = 'Basic Price', p.[sold] * p.[price], NULL)) AS SoldBasic,
[PurchasedBasic] - [SoldBasic] AS DiffBasic,
SUM(IIF(p.Type = 'Low Price', p.[purchased] * p.[price], NULL)) AS PurchasedLowPrice,
SUM(IIF(p.Type = 'Low Price', p.[sold] * p.[price], NULL)) AS SoldLowPrice,
[PurchasedLowPrice] - [SoldLowPrice] AS DiffLowPrice
FROM Departments d
LEFT JOIN Prices p ON d.departmend_id = p.departmend_id
GROUP BY d.departmend_id,
d.department_name;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/359370.html