Select MD002 AS "元件",sum(all) AS "总用量" from ( Select n.MC001,m.MD002,m.total*n.MC002 as all from (select MD001,MD002,count(*) as total from BOM group by MD001,MD002) m, INV n where m.MD001 = n.MC001) x group by MD002
自己來解。 建測試表: CREATE TABLE BOM ( MD001 NVARCHAR(5), MD002 NVARCHAR(5), MD003 INT )CREATE TABLE INV ( MC001 NVARCHAR(5), MC002 INT )INSERT INTO BOM SELECT 'A','M1', 2 UNION ALL SELECT 'A','M2', 10 UNION ALL SELECT 'A', 'M3', 6 UNION ALL SELECT 'B', 'M1', 5 UNION ALL SELECT 'B', 'M4', 1 UNION ALL SELECT 'C', 'M1', 2 UNION ALL SELECT 'C', 'M3', 1 UNION ALL SELECT 'C', 'M4', 8INSERT INTO INV SELECT 'A', 2000 UNION ALL SELECT 'B', 800 UNION ALL SELECT 'C', 1500 逆算成品使用的原材料的用量匯總數: SELECT MD002,SUM(QTY) AS QTY FROM ( SELECT A.MD002,A.MD003*B.MC002 AS QTY FROM BOM A INNER JOIN INV B ON A.MD001=B.MC001 WHERE A.MD001=B.MC001 ) AS M GROUP BY MD002 結果:MD002 QTY ----- ----------- M1 11000 M2 20000 M3 13500 M4 12800(4 row(s) affected) DROP TABLE BOM DROP TABLE INV
from
(
Select n.MC001,m.MD002,m.total*n.MC002 as all
from
(select MD001,MD002,count(*) as total
from BOM
group by MD001,MD002) m,
INV n
where m.MD001 = n.MC001) x
group by MD002
建測試表:
CREATE TABLE BOM
(
MD001 NVARCHAR(5),
MD002 NVARCHAR(5),
MD003 INT
)CREATE TABLE INV
(
MC001 NVARCHAR(5),
MC002 INT
)INSERT INTO BOM
SELECT 'A','M1', 2
UNION ALL SELECT 'A','M2', 10
UNION ALL SELECT 'A', 'M3', 6
UNION ALL SELECT 'B', 'M1', 5
UNION ALL SELECT 'B', 'M4', 1
UNION ALL SELECT 'C', 'M1', 2
UNION ALL SELECT 'C', 'M3', 1
UNION ALL SELECT 'C', 'M4', 8INSERT INTO INV
SELECT 'A', 2000
UNION ALL SELECT 'B', 800
UNION ALL SELECT 'C', 1500 逆算成品使用的原材料的用量匯總數: SELECT MD002,SUM(QTY) AS QTY
FROM
(
SELECT A.MD002,A.MD003*B.MC002 AS QTY FROM BOM A INNER JOIN INV B
ON A.MD001=B.MC001
WHERE A.MD001=B.MC001
) AS M
GROUP BY MD002 結果:MD002 QTY
----- -----------
M1 11000
M2 20000
M3 13500
M4 12800(4 row(s) affected) DROP TABLE BOM
DROP TABLE INV