SQL 指令要如何下?

sql 指令

XYZ 2019-03-12 12:23:51 ‧ 2477 瀏覽

分享至

請問下面需求的欄位要如何下SQL

AM01是1月的金額、AM02是2月的金額、AM03是3月的金額、AM04是4月的金額........AM12是12月的金額。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

若a=4 是收入

如果選1月，則金額是加總1月同品名的AM01欄位的值 as 收入總金額
如果選2月，則金額是加總1月~2月同品名的AM01+AM02欄位的值 as 收入總金額
如果選3月，則金額是加總1月~3月同品名的AM01+AM02+AM03欄位的值 as 收入總金額
如果選4月，則金額是加總1月~4月同品名的AM01+AM02+AM03+AM04欄位的值 as 收入總金額
.
.
.
如果選12月，則金額是加總1月~12月同品名的AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10+AM11+AM12欄位的值

若a=5 是支出

如果選1月，則金額是加總1月同品名的AM01欄位的值 as 支出總金額
如果選2月，則金額是加總1月~2月同品名的AM01+AM02欄位的值 as 支出總金額
如果選3月，則金額是加總1月~3月同品名的AM01+AM02+AM03欄位的值 as 支出總金額
如果選4月，則金額是加總1月~4月同品名的AM01+AM02+AM03+AM04欄位的值 as 支出總金額
.
.
.
如果選12月，則金額是加總1月~12月同品名的AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10+AM11+AM12欄位的值

不好意思，描述不清楚，造成誤解，附圖說明:

登入發表討論

直播研討會

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

直播中

6 個回答

純真的人

iT邦大師 1 級 ‧ 2019-03-12 18:35:55

最佳解答

declare @tab table(
	partnum nvarchar(50)
	,a nvarchar(50)
	,AM01 int
	,AM02 int
	,AM03 int
	,AM04 int
	,AM05 int
	,AM06 int
	,AM07 int
	,AM08 int
	,AM09 int
	,AM10 int
	,AM11 int
	,AM12 int
)
insert into @tab
VALUES
('aa',4,1,2,3,4,5,6,7,8,9,10,11,12),
('aa',4,2,4,6,8,10,12,14,16,18,20,22,24),
('aa',5,1,2,3,4,5,6,7,8,9,10,11,12),
('aa',5,2,4,6,8,10,12,14,16,18,20,22,24),
('aa',5,3,6,9,12,15,18,21,24,27,30,33,36),
('bb',4,10,20,30,40,50,60,70,80,90,100,110,120),
('bb',4,20,40,60,80,100,120,140,160,180,200,220,240),
('bb',5,10,20,30,40,50,60,70,80,90,100,110,120),
('bb',5,20,40,60,80,100,120,140,160,180,200,220,240),
('bb',5,30,60,90,120,150,180,210,240,270,300,330,360);

declare @Num int
set @Num = 4

select partnum
,Sum(
	case when a = 4 
	then (
		case @Num
		when 1 then AM01
		when 2 then AM01+AM02
		when 3 then AM01+AM02+AM03
		when 4 then AM01+AM02+AM03+AM04
		when 5 then AM01+AM02+AM03+AM04+AM05
		when 6 then AM01+AM02+AM03+AM04+AM05+AM06
		when 7 then AM01+AM02+AM03+AM04+AM05+AM06+AM07
		when 8 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08
		when 9 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09
		when 10 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10
		when 11 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10+AM11
		when 12 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10+AM11+AM12
		else 0
		end
	)
	else 0
	end
) as 收入金額
,Sum(
	case when a = 5 
	then (
		case @Num
		when 1 then AM01
		when 2 then AM01+AM02
		when 3 then AM01+AM02+AM03
		when 4 then AM01+AM02+AM03+AM04
		when 5 then AM01+AM02+AM03+AM04+AM05
		when 6 then AM01+AM02+AM03+AM04+AM05+AM06
		when 7 then AM01+AM02+AM03+AM04+AM05+AM06+AM07
		when 8 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08
		when 9 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09
		when 10 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10
		when 11 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10+AM11
		when 12 then AM01+AM02+AM03+AM04+AM05+AM06+AM07+AM08+AM09+AM10+AM11+AM12
		else 0
		end
	)
	else 0
	end
) as 支出金額
from @tab
group by partnum

回應 1
分享
檢舉

XYZ iT邦新手 4 級 ‧ 2019-03-13 11:52:53 檢舉

感謝您，幫我解惑。

登入發表回應

paicheng0111

iT邦大師 5 級 ‧ 2019-03-12 12:55:27

改成下面這樣會不會比較好？

year	month	amt
2019	1	100
2019	2	200
2019	3	300
2019	4	400
2019	5	500
2019	6	600
2019	7	700
2019	8	800
2019	9	900
2019	10	1000
2019	11	1100
2019	12	1200

回應 1
分享
檢舉

XYZ iT邦新手 4 級 ‧ 2019-03-12 14:13:45 檢舉

不好意思,目前的報表需求是我上面列的.

登入發表回應

rogeryao

iT邦超人 8 級 ‧ 2019-03-12 14:21:04

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b1735cd15d5bb9477b53748b2285d10a

已更新

回應 1
分享
檢舉

XYZ iT邦新手 4 級 ‧ 2019-03-12 16:10:42 檢舉

不好意思，描述不清楚，造成誤解，附圖說明了，請幫忙解惑，謝謝。

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2019-03-12 15:01:10

-- 這種叫做 Cumulative Sum 或是 Running Total
-- 使用 Postgresql為例
-- 產生測試資料,12個月,每個月都是100

create table ithelp190312 (
  id smallint generated always as identity
, cdate date not null
, amount int not null
);

insert into ithelp190312 (cdate, amount)
select ts::date
     , 100
  from generate_series('2019-01-01'::timestamp
                      ,'2019-12-01'::timestamp
                      ,'1 months') as gs(ts);
--
select *
     , sum(amount) 
       over(order by cdate asc rows between unbounded preceding and current row) 
       as "Cumulative Sum"
  from ithelp190312;

+----+------------+--------+----------------+
| id |   cdate    | amount | Cumulative Sum |
+----+------------+--------+----------------+
|  1 | 2019-01-01 |    100 |            100 |
|  2 | 2019-02-01 |    100 |            200 |
|  3 | 2019-03-01 |    100 |            300 |
|  4 | 2019-04-01 |    100 |            400 |
|  5 | 2019-05-01 |    100 |            500 |
|  6 | 2019-06-01 |    100 |            600 |
|  7 | 2019-07-01 |    100 |            700 |
|  8 | 2019-08-01 |    100 |            800 |
|  9 | 2019-09-01 |    100 |            900 |
| 10 | 2019-10-01 |    100 |           1000 |
| 11 | 2019-11-01 |    100 |           1100 |
| 12 | 2019-12-01 |    100 |           1200 |
+----+------------+--------+----------------+
(12 rows)

-- 另外,你們問問題,最好能夠自己準備基本資料,跟想達成的.
-- 還有發問的標題,不要這種語焉不詳的,先把中文好好學好,意思表達清楚.
-- 人自己想清楚,程式自然就清楚了.

回應 2
分享
檢舉

XYZ iT邦新手 4 級 ‧ 2019-03-12 16:10:33 檢舉

不好意思，描述不清楚，造成誤解，附圖說明了，請幫忙解惑，謝謝。

一級屠豬士 iT邦大師 1 級 ‧ 2019-03-12 23:56:23 檢舉

你的原始Table設計不好,該用月份與數值,不是這樣開欄位.
設計方向要依照資料庫的觀念,而不是一路加欄位.

登入發表回應

fuzzylee1688

iT邦研究生 3 級 ‧ 2019-03-12 17:41:31

已刪

回應
分享
檢舉

登入發表回應

thwu

iT邦新手 5 級 ‧ 2019-03-13 11:37:06

IF OBJECT_ID('tempdb..#Data') IS NOT NULL 
BEGIN 
	DROP TABLE #Data 
END

CREATE TABLE #Data (
	PartNum varchar(20),
	[a] int,
	AM01 int,
	AM02 int,
	AM03 int,
	AM04 int,
	AM05 int,
	AM06 int,
	AM07 int,
	AM08 int,
	AM09 int,
	AM010 int,
	AM011 int,
	AM012 int
)

INSERT INTO #Data
	(PartNum, [a], AM01, AM02, AM03, AM04, AM05, AM06, AM07, AM08, AM09, AM010, AM011, AM012)
VALUES
	('aa', 4, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12),
	('aa', 4, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24),
	('bb', 4, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120),
	('bb', 4, 20, 40, 60, 80, 100, 120, 140, 160, 180, 200, 220, 240),
	('bb', 5, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120),
	('bb', 5, 20, 40, 60, 80, 100, 120, 140, 160, 180, 200, 220, 240),
	('bb', 5, 30, 60, 90, 120, 150, 180, 210, 240, 270, 300, 330, 360),
	('aa', 5, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12),
	('aa', 5, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24),
	('aa', 5, 3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33, 36)

DECLARE @Month INT
SET @Month = 4

SELECT
	*
FROM
	--	再 PIVOT
	(SELECT data.PartNum, CASE data.a WHEN 4 THEN '收入金額' WHEN 5 THEN '支出金額' END AS 'AmountType', SUM([Amount]) AS 'Amount'
	FROM 
		--	先 UNPIVOT
		(SELECT pv.PartNum, pv.[a], pv.Amount, REPLACE(pv.[Month], 'AM','') AS 'Month'
		FROM #Data
			UNPIVOT ([Amount] FOR [Month] IN (AM01, AM02, AM03, AM04, AM05, AM06, AM07, AM08, AM09, AM010, AM011, AM012)
			) pv
		) data
	WHERE data.[Month] <= @Month
	GROUP BY data.PartNum, data.a
	) GroupTable
	PIVOT (SUM([Amount]) FOR [AmountType] IN ([收入金額], [支出金額])
	) PivotTable

DROP TABLE #Data

一般都是 raw data 經過 pivot 後才是樓主貼出來的資料
所以先經過 unpivot 轉回去 raw data 後比較容易過濾資料，最後再 pivot 回去成統計資料。

回應 1
分享
檢舉

純真的人 iT邦大師 1 級 ‧ 2019-03-13 13:21:41 檢舉

這方式不錯~~
試了重寫練習一下^^~

select *
from (
	SELECT PartNum
	,CASE a WHEN 4 THEN '收入金額' WHEN 5 THEN '支出金額' END AS 'AmountType', SUM([Amount]) AS 'Amount'
	FROM @tab
	UNPIVOT (
		[Amount] FOR [Month] IN (AM01, AM02, AM03, AM04, AM05, AM06, AM07, AM08, AM09, AM10, AM11, AM12)
	) pv
	where REPLACE([Month], 'AM','') <= @Month
	group by PartNum
	,a
) as k
PIVOT (
	SUM([Amount]) FOR [AmountType] IN ([收入金額], [支出金額])
) PivotTable

登入發表回應