老了, 求教个SQL 语句写法, 请大家帮忙
有如下表格, 有个恒定值30
F0 F1 F2 F3
----------------------
A1 20 22 16
A2 8 6 4
A3 18 2 -10
求个简单点的语句算出如上红色那一行A3的数据,
计算原则是比如A3第一个数18 = 30 - 20 + 8
然后第二个数 2 = 18 - 22 + 6, 里面的18就是前面一列算出来的18
依此类推...
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (F0 nvarchar(4),F1 int,F2 int,F3 int)
insert into [TB]
select 'A1',20,22,16 union all
select 'A2',8,6,4
;WITH TH
AS(
SELECT num,A1
FROM (select F0,CASE WHEN F0='A2' THEN -F1 ELSE F1 END AS F1,
CASE WHEN F0='A2' THEN -F2 ELSE F2 END AS F2,
CASE WHEN F0='A2' THEN -F3 ELSE F3 END AS F3
from [TB])B
UNPIVOT(A1 FOR num IN(F1,F2,F3))AS u),
TF
AS(
SELECT num,SUM(A1) AS A1
FROM TH
GROUP BY num),
FF
AS(
SELECT B.num,30 -SUM(A.A1) AS 'A3'
FROM TF A
INNER JOIN TF B ON A.num <=B.num
GROUP by b.num)
SELECT F0,F1,F2,F3 FROM dbo.TB
UNION all
SELECT 'A3' AS F0,
F1,F2,F3
FROM FF
PIVOT(MAX(A3)
FOR num IN (F1,F2,F3))U
/*
F0F1F2F3
A1202216
A2864
A3182-10*/
CREATE TABLE t1
(
f0 VARCHAR(10),
f1 INT,
f2 INT,
f3 INT
)
INSERT INTO t1
SELECT 'A1',20,22,16 UNION ALL
SELECT 'A2',8,6,4
SELECT * FROM t1
;WITH AAA AS
(
SELECTROW_NUMBER() OVER(ORDER BY f0 DESC) AS Rowindex,
*
FROMt1
)
,BBB AS
(
SELECTA.f0,
A.f1 AS f11,
A.f2 AS f21,
A.f3 AS f31,
B.f1 AS f12,
B.f2 AS f22,
B.f3 AS f32
FROMAAA AS A INNER JOIN
AAA AS B ON A.Rowindex=B.Rowindex-1
ANDA.Rowindex=1
)
--SELECT * FROM BBB
INSERT INTO t1
(
f0,
f1,
f2,
f3
)
SELECTLEFT(f0,1)+LTRIM(CAST(RIGHT(f0,LEN(f0)-1) AS INT)+1) AS f0,
30-f12+f11 AS [f1],
30-f12+f11-f22+f21 AS [f2],
30-f12+f11-f22+f21-f32+f31 AS [f3]
FROMBBB
---------------------------------------------------
f0f1f2f3
A1202216
A2864
A3182-10