0

## MS SQL 題目問題

1,2,4是入帳所以為正數
3是扣帳所以要轉成負數

4 入帳 100

sum 跟group 試試吧

3為扣帳100，因先減掉1 入帳50後

3為扣帳100，因先減掉1 入帳50後

### 2 個回答

0
dog830228
iT邦研究生 3 級 ‧ 2017-09-01 10:47:21

``````-- [type] A 為入帳
-- [type] B 為出帳
CREATE TABLE #Temp
(
[type] VARCHAR(10),
Price  INT
)
INSERT INTO #Temp ([type],Price)

VALUES
('A',50),
('A',100),
('B',100),
('A',50)

SELECT
TOP 1
(SELECT SUM(PRICE)FROM #TEMP WHERE [type]='A') - (SELECT SUM(PRICE)FROM #TEMP WHERE [type]='B') AS Totle
FROM #TEMP

``````

1 入帳 100

C欄位從 1和2計算後的數

dog830228 iT邦研究生 3 級 ‧ 2017-09-01 15:34:10 檢舉

``````-- [type] A 為入帳
-- [type] B 為出帳
CREATE TABLE #Temp
(
Sn     BIGINT,
[type] VARCHAR(10),
Price  INT
)
DECLARE @Index BIGINT = 1
DECLARE @MAXCount BIGINT
DECLARE @TotlePrice BIGINT = 0
DECLARE @TempType   VARCHAR(10)
DECLARE @TempPrice  INT

INSERT INTO #Temp (Sn,[type],Price)
VALUES
(1,'A',50),
(2,'A',100),
(3,'B',100),
(4,'A',50)

SET @MAXCount = (SELECT COUNT(sn) FROM #TEMP)

WHILE(@Index <= @MAXCount)
BEGIN
SET @TempType  =  (SELECT [type] FROM #Temp WHERE sn=@Index)
SET @TempPrice =  (SELECT PRICE FROM #Temp WHERE sn=@Index)

IF  @TempType = 'A'
BEGIN
SET @TotlePrice = @TotlePrice + @TempPrice
END
ELSE IF @TempType = 'B'
BEGIN
SET @TotlePrice = @TotlePrice - @TempPrice
END

PRINT(@TempType)
PRINT(@TotlePrice)
SET @Index = @Index + 1
END

--SELECT @TotlePrice AS Totle

drop table #Temp
``````

dog830228 iT邦研究生 3 級 ‧ 2017-09-04 13:15:03 檢舉

DECLARE @Index BIGINT = 1
DECLARE @TotlePrice BIGINT = 0

## 下載太慢了，結果我直接在2005 宣告後SET變數值，就可以了 不過答案還有點出路 這題答案是4 A 100 是因為最後入庫50 所以停在4 A 100

sn3為B,100

@Index和@TempType應停在sn2的地方也就是 2 A
@TotlePrice為最後結果50

dog830228 iT邦研究生 3 級 ‧ 2017-09-04 17:04:59 檢舉

OK 非常感謝!!

0

iT邦高手 1 級 ‧ 2017-09-04 14:49:07

declare @table as Table(
a int
,b nvarchar(50)
,c int
)

insert into @table
values(1,'入帳',50)
,(2,'入帳',100)
,(3,'扣帳',100)
,(4,'入帳',50)

select Sum(case when b = '扣帳' then -c else c end) as Sum_Cash from @table