Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tableName
(
分群 VARCHAR(50),
階數 VARCHAR(50),
重計投入量 INT,
產出_M2 INT,
邊料回算_M2 INT
);
INSERT INTO tableName
VALUES ('A','1',150,120,30);
1 rows affected

INSERT INTO tableName
VALUES ('A','1',1500,1200,300);
1 rows affected

INSERT INTO tableName
VALUES ('A','2',2510,2120,130);
1 rows affected

INSERT INTO tableName
VALUES ('A','2',2510,2220,230);
1 rows affected

INSERT INTO tableName
VALUES ('B','1',5910,5520,530);
1 rows affected

INSERT INTO tableName
VALUES ('B','1',6900,6600,600);
1 rows affected

INSERT INTO tableName
VALUES ('B','2',7910,7720,730);
1 rows affected

INSERT INTO tableName
VALUES ('B','2',8910,8820,830);
1 rows affected

INSERT INTO tableName
VALUES ('D','1',18910,18820,1830);
1 rows affected

SELECT *
FROM tableName


分群
階數
重計投入量
產出_M2
邊料回算_M2


A
1
150
120
30

A
1
1500
1200
300

A
2
2510
2120
130

A
2
2510
2220
230

B
1
5910
5520
530

B
1
6900
6600
600

B
2
7910
7720
730

B
2
8910
8820
830

D
1
18910
18820
1830


CREATE TABLE YIELD_NAME 
(
品目類別 VARCHAR(50)
);
INSERT INTO YIELD_NAME
VALUES ('A');
1 rows affected

INSERT INTO YIELD_NAME
VALUES ('B');
1 rows affected

INSERT INTO YIELD_NAME
VALUES ('C');
1 rows affected

SELECT *
FROM YIELD_NAME


品目類別


A

B

C


-- 版主原 SQL : 去除 FACTORY,工單別,結案日 三個條件
with t1 as (
SELECT 分群, sum(重計投入量) 投入量1,sum(產出_M2) 入庫量1,  LEAST(sum(產出_M2)/sum(重計投入量)*100,100) 量產1
from tableName 
where 階數='1' 
group by 分群)

, t2 as (
SELECT 分群, sum(重計投入量) 發料量,sum(產出_M2) 入庫量2,sum(邊料回算_M2) 入庫量2回,  LEAST((sum(產出_M2)-sum(邊料回算_M2))/sum(重計投入量)*100,100) 量產2, LEAST(sum(產出_M2)/sum(重計投入量)*100,100) 量產2回
from tableName 
where 階數='2' 
group by 分群)

,t3 as(SELECT 品目類別 from YIELD_NAME)

select t3.品目類別, round(t1.投入量1,0) 投入量1, round(t1.入庫量1,0) 入庫量1, round(t1.量產1,2) 量產1
, round(t2.發料量,0) 發料量, round(t2.入庫量2,0) - round(t2.入庫量2回,0) 入庫量2, round(t2.量產2,2) 量產2, 
round(t1.量產1*t2.量產2/100,2) 量產,round(t2.量產2回,2) 量產2回,
round(t1.量產1*t2.量產2回/100,2) 量產回,round(t2.入庫量2回,0) 工單入庫量2回
from t1 full join t2 on (t1.分群=t2.分群) full join t3 on(t1.分群=t3.品目類別) where t3.品目類別 is not null 

order by 1;


品目類別
投入量1
入庫量1
量產1
發料量
入庫量2
量產2
量產
量產2回
量產回
工單入庫量2回


A
1650
1320
80
5020
3980
79.28
63.43
86.45
69.16
360

B
12810
12120
94.61
16820
14980
89.06
84.26
98.34
93.04
1560

C
null
null
null
null
null
null
null
null
null
null


-- 方法一 : 所有欄位皆加總
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)

SELECT
COALESCE (K.品目類別, '總計') 品目類別,
ROUND(SUM(K.投入量1), 0) 投入量1,
ROUND(SUM(K.入庫量1), 0) 入庫量1,
ROUND(SUM(K.量產1), 2) 量產1,
ROUND(SUM(K.發料量), 0) 發料量,
ROUND(SUM(K.入庫量2), 0) - ROUND(SUM(K.入庫量2回), 0) 入庫量2,
ROUND(SUM(K.量產2), 2) 量產2,
ROUND(SUM(K.量產1 * K.量產2) / 100, 2) 量產,
ROUND(SUM(K.量產2回), 2) 量產2回,
ROUND(SUM(K.量產1 * K.量產2回) / 100, 2) 量產回,
ROUND(SUM(K.入庫量2回), 0) 工單入庫量2回
FROM K
GROUP BY ROLLUP (K.品目類別);


品目類別
投入量1
入庫量1
量產1
發料量
入庫量2
量產2
量產
量產2回
量產回
工單入庫量2回


A
1650
1320
80
5020
3980
79.28
63.43
86.45
69.16
360

B
12810
12120
94.61
16820
14980
89.06
84.26
98.34
93.04
1560

C
0
0
0
0
0
0
0
0
0
0

總計
14460
13440
174.61
21840
18960
168.34
147.69
184.79
162.2
1920


-- 方法二 : 部分欄位加總
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)
,F1 AS (
SELECT
COALESCE (K.品目類別, '總計') 品目類別,
ROUND(SUM(K.投入量1), 0) 投入量1,
ROUND(SUM(K.入庫量1), 0) 入庫量1,
ROUND(SUM(K.發料量), 0) 發料量,
ROUND(SUM(K.入庫量2), 0) - ROUND(SUM(K.入庫量2回), 0) 入庫量2,
ROUND(SUM(K.入庫量2回), 0) 工單入庫量2回
FROM K 
GROUP BY ROLLUP (K.品目類別)
)
,F2 AS (
SELECT
K.品目類別 品目類別,
ROUND(K.量產1, 2) 量產1,
ROUND(K.量產2, 2) 量產2,
ROUND((K.量產1 * K.量產2) / 100, 2) 量產,
ROUND(K.量產2回, 2) 量產2回,
ROUND((K.量產1 * K.量產2回) / 100, 2) 量產回
FROM K 
)

SELECT F1.品目類別,
F1.投入量1,
F1.入庫量1,
F2.量產1,
F1.發料量,
F1.入庫量2,
F2.量產2,
F2.量產,
F2.量產2回,
F2.量產回,
F1.工單入庫量2回
FROM F1
LEFT JOIN F2 ON F1.品目類別=F2.品目類別


品目類別
投入量1
入庫量1
量產1
發料量
入庫量2
量產2
量產
量產2回
量產回
工單入庫量2回


A
1650
1320
80
5020
3980
79.28
63.43
86.45
69.16
360

B
12810
12120
94.61
16820
14980
89.06
84.26
98.34
93.04
1560

C
0
0
0
0
0
0
0
0
0
0

總計
14460
13440
null
21840
18960
null
null
null
null
1920


-- 方法三 : 部分欄位加總，【純真的人】用的 UNION 方法
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)
,P AS (
SELECT
K.品目類別 品目類別,
ROUND(K.投入量1, 0) 投入量1,
ROUND(K.入庫量1, 0) 入庫量1,
ROUND(K.量產1, 2) 量產1,
ROUND(K.發料量, 0) 發料量,
ROUND(K.入庫量2, 0) - ROUND(K.入庫量2回, 0) 入庫量2,
ROUND(K.量產2, 2) 量產2,
ROUND(K.量產1 * K.量產2 / 100, 2) 量產,
ROUND(K.量產2回, 2) 量產2回,
ROUND(K.量產1 * K.量產2回 / 100, 2) 量產回,
ROUND(K.入庫量2回, 0) 工單入庫量2回
FROM K
)
 
SELECT *
FROM P
UNION
SELECT 
'總計' 品目類別,
SUM(投入量1) 投入量1,
SUM(入庫量1) 入庫量1,
NULL 量產1,
SUM(發料量) 發料量,
SUM(入庫量2) 入庫量2,
NULL 量產2,
NULL 量產,
NULL 量產2回,
NULL 量產回,
SUM(工單入庫量2回) 工單入庫量2回
FROM P


品目類別
投入量1
入庫量1
量產1
發料量
入庫量2
量產2
量產
量產2回
量產回
工單入庫量2回


A
1650
1320
80
5020
3980
79.28
63.43
86.45
69.16
360

B
12810
12120
94.61
16820
14980
89.06
84.26
98.34
93.04
1560

C
0
0
0
0
0
0
0
0
0
0

總計
14460
13440
null
21840
18960
null
null
null
null
1920
分群	階數	重計投入量	產出_M2	邊料回算_M2
A	1	150	120	30
A	1	1500	1200	300
A	2	2510	2120	130
A	2	2510	2220	230
B	1	5910	5520	530
B	1	6900	6600	600
B	2	7910	7720	730
B	2	8910	8820	830
D	1	18910	18820	1830
品目類別	投入量1	入庫量1	量產1	發料量	入庫量2	量產2	量產	量產2回	量產回	工單入庫量2回
A	1650	1320	80	5020	3980	79.28	63.43	86.45	69.16	360
B	12810	12120	94.61	16820	14980	89.06	84.26	98.34	93.04	1560
C	null	null	null	null	null	null	null	null	null	null