日期:2014-05-17 浏览次数:20548 次
select 'A3',f1=30-a.f1+b.f1,f2=30-a.f1+b.f1-a.f2+b.f2,f3=30-a.f1+b.f1-a.f2+b.f2-a.f3+b.f3 from tb a,tb b where a.f0='A1' and b.f0='A2'
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
/*
F0 F1 F2 F3
A1 20 22 16
A2 8 6 4
A3 18 2 -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
(
SELECT ROW_NUMBER() OVER(ORDER BY f0 DESC) AS Rowindex,
*
FROM t1
)
,BBB AS
(
SELECT A.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
FROM AAA AS A INNER JOIN
AAA AS B ON A.Rowindex=B.Rowindex-1
AND A.Rowindex=1
)
--SELECT * FROM BBB
INSERT INTO t1
(
f0,
f1,
f2,
f3
)
SELECT LEFT(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],