MSSQL 不良率呈現

mssql 不良率呈現

noway 2024-06-05 16:43:48 ‧ 1530 瀏覽

分享至

您好:於MSSQL 中。資料類似如下
是否可以轉成中間那個不良率的計算筆格方式呈現

SELECT * FROM (
	SELECT 'A' '庫別', '01' '日期', '螺絲-次' '類型',2 '數量'
	UNION ALL 
	SELECT 'A' '庫別', '01' '日期', '螺絲-全' '類型',1000 '數量'
	UNION ALL 
	SELECT 'B' '庫別', '01' '日期', '螺絲-次' '類型',3 '數量'
	UNION ALL 
	SELECT 'B' '庫別', '01' '日期', '螺絲-全' '類型',1100 '數量'
) a

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2024-06-05 20:17:31

最佳解答

Create table aa (
X1 NVarchar(10),
X2 NVarchar(10),
X3 NVarchar(10),
X4 Float);

Insert Into aa Values
('B','02',N'螺絲-全',1300),
('B','02',N'螺絲-次',5),
('A','02',N'螺絲-次',4),
('A','02',N'螺絲-全',1200),
('A','01',N'螺絲-次',2),
('A','01',N'螺絲-全',1000),
('B','01',N'螺絲-次',3),
('B','01',N'螺絲-全',1100);

Select *
From (
Select X1,X2,X3,X4 
From aa
Union 
Select T1.X1 AS X1,T1.X2 AS X2,N'螺絲-率' AS X3,
Round(T1.X4 / T2.X4,6) AS X4
From aa AS T1  
Left Join aa AS T2 On T1.X1 = T2.X1 And T1.X2 = T2.X2
Where T1.X3 = N'螺絲-次'
And T2.X3 = N'螺絲-全') AS MM
--
PIVOT ( 
SUM(X4) 
FOR X2 IN ([01],[02]) 
) AS QQ
Order By X1,(Case When X3 = N'螺絲-次' then 1
                  When X3 = N'螺絲-全' then 2
                  When X3 = N'螺絲-率' then 3
                  Else 4 End)

Demo

回應 6
分享
檢舉

看更多先前的回應...收起先前的回應...

noway iT邦研究生 1 級 ‧ 2024-06-06 13:35:49 檢舉

有了，謝謝!
重點在於
Select T1.X1 AS X1,T1.X2 AS X2,N'螺絲-率' AS X3,
Round(T1.X4 / T2.X4,6) AS X4
From aa AS T1
Left Join aa AS T2 On T1.X1 = T2.X1 And T1.X2 = T2.X2
Where T1.X3 = N'螺絲-次'
And T2.X3 = N'螺絲-全'
這一段，另外產生一筆資料

我還傻傻的把他原來資料先寫入暫存檔，再抓出來一筆比率處理
謝謝

noway iT邦研究生 1 級 ‧ 2024-06-12 11:11:30 檢舉

您好：
若今天再加一個　年月欄位

Create table aa (
YYMM NVARCHAR(5),
X1 NVarchar(10),
X2 NVarchar(10),
X3 NVarchar(10),
X4 Float);

Insert Into aa Values
('11304','B','02',N'螺絲-全',1300),
('11304','B','02',N'螺絲-次',5),
('11304','A','02',N'螺絲-次',4),
('11304','A','02',N'螺絲-全',1200),
('11304','A','01',N'螺絲-次',2),
('11304','A','01',N'螺絲-全',1000),
('11304','B','01',N'螺絲-次',3),
('11304','B','01',N'螺絲-全',1100),

('11305','B','02',N'螺絲-全',1300),
('11305','B','02',N'螺絲-次',5),
('11305','A','02',N'螺絲-次',4),
('11305','A','02',N'螺絲-全',1200),
('11305','A','01',N'螺絲-次',2),
('11305','A','01',N'螺絲-全',1000),
('11305','B','01',N'螺絲-次',3),
('11305','B','01',N'螺絲-全',1100),

這樣若用原來的方式，　他會以日期壘加
但我要的是　yymm -日期　，獨立一筆
有辦法　多欄位　ｐｉｖｏｔ嗎？
謝謝

rogeryao iT邦超人 7 級 ‧ 2024-06-12 14:28:55 檢舉

看無,請貼結果示意圖

noway iT邦研究生 1 級 ‧ 2024-06-12 16:00:55 檢舉

您好：
如附圖，多一個欄位　ＹＹＭＭ，
相對的,pivot也會多一個yymm
一開始以為會重複加總
但有找到原因，
不過仍想知道多欄位的ＰＩＶＯＴ
謝謝

rogeryao iT邦超人 7 級 ‧ 2024-06-12 16:38:20 檢舉

Create table aa (
YYMM NVARCHAR(5),
X1 NVarchar(10),
X2 NVarchar(10),
X3 NVarchar(10),
X4 Float);

Insert Into aa Values
('11304','B','02',N'螺絲-全',1300),
('11304','B','02',N'螺絲-次',5),
('11304','A','02',N'螺絲-次',4),
('11304','A','02',N'螺絲-全',1200),
('11304','A','01',N'螺絲-次',2),
('11304','A','01',N'螺絲-全',1000),
('11304','B','01',N'螺絲-次',3),
('11304','B','01',N'螺絲-全',1100),

('11305','B','02',N'螺絲-全',1300),
('11305','B','02',N'螺絲-次',15),
('11305','A','02',N'螺絲-次',14),
('11305','A','02',N'螺絲-全',1200),
('11305','A','01',N'螺絲-次',12),
('11305','A','01',N'螺絲-全',1000),
('11305','B','01',N'螺絲-次',13),
('11305','B','01',N'螺絲-全',1100);

Select *
From (
Select YYMM,X1,X2,X3,X4 
From aa
Union 
Select T1.YYMM,T1.X1 AS X1,T1.X2 AS X2,N'螺絲-率' AS X3,
Round(T1.X4 / T2.X4,6) AS X4
From aa AS T1  
Left Join aa AS T2 On T1.X1 = T2.X1 And T1.X2 = T2.X2  And T1.YYMM = T2.YYMM
Where T1.X3 = N'螺絲-次'
And T2.X3 = N'螺絲-全') AS MM
--
PIVOT ( 
SUM(X4) 
FOR X2 IN ([01],[02]) 
) AS QQ
Order By YYMM,X1,(Case When X3 = N'螺絲-次' then 1
                  When X3 = N'螺絲-全' then 2
                  When X3 = N'螺絲-率' then 3
                  Else 4 End)

Demo

noway iT邦研究生 1 級 ‧ 2024-06-13 09:16:12 檢舉

謝謝

登入發表回應

pilipala

iT邦研究生 5 級 ‧ 2024-06-06 10:44:38

主要是用 Pivot 來達到需求

use tempdb
go

DROP TABLE IF EXISTS tblQ

CREATE TABLE tblQ (庫別 char(1) , 日期 char(2) , 類別 nvarchar(10) , 數量 int)
INSERT INTO tblQ (庫別 , 日期 , 類別 , 數量)
SELECT 'A' , '01' , N'螺絲-次' ,2 
UNION ALL  		  
SELECT 'A' , '01' , N'螺絲-全' ,1000 
UNION ALL  		  
SELECT 'B' , '01' , N'螺絲-次' ,3 
UNION ALL  		  
SELECT 'B' , '01' , N'螺絲-全' ,1100
UNION ALL  		  
-- 以下為自行加入資料
SELECT 'C' , '02' , N'螺絲-次' , 999
UNION ALL  		  
SELECT 'C' , '02' , N'螺絲-全' , 0

-- Q1
SELECT *
FROM
	(
		SELECT 庫別 , 日期 , 類別 , 數量
		FROM tblQ
		UNION ALL
		SELECT 庫別 , 日期 , N'螺絲-率' , [螺絲-次] * 1.0 / NULLIF([螺絲-全] , 0) * 1.0
		FROM tblQ AS P
			PIVOT
				(
					SUM(數量) FOR 類別 IN ([螺絲-次] , [螺絲-全])
				) AS PV
	) AS F
ORDER BY 
	庫別 , 
	日期 , 
	CASE 類別 
		WHEN N'螺絲-次' THEN 1
		WHEN N'螺絲-全' THEN 2
		WHEN N'螺絲-率' THEN 3
		ELSE 4
	END , 
	數量

-- Q2
;
WITH CTE AS
(
	SELECT 庫別 , 日期 , 類別 , 數量
	FROM tblQ
	UNION ALL
	SELECT 庫別 , 日期 , N'螺絲-率' , [螺絲-次] * 1.0 / NULLIF([螺絲-全] , 0) * 1.0
	FROM tblQ AS P
		PIVOT
			(
				SUM(數量) FOR 類別 IN ([螺絲-次] , [螺絲-全])
			) AS PV
)
SELECT PV.* 
FROM CTE AS P
	PIVOT
	(
		SUM(數量) FOR 日期 IN ([01] , [02])
	) AS PV
ORDER BY 
	庫別 , 
	CASE 類別 
		WHEN N'螺絲-次' THEN 1
		WHEN N'螺絲-全' THEN 2
		WHEN N'螺絲-率' THEN 3
		ELSE 4
	END