表A有如下字段
时间段 类型A 类型B 数量
1 a a 10
1 a b 20
1 b a 30
2 a b 45
2 b a 70
2 b b 100希望得到以下结果
类型A 类型B 数量
a a -10
a b 25
b a 40
b b 100也就是求时间段2里面对时间段1的增量
时间段 类型A 类型B 数量
1 a a 10
1 a b 20
1 b a 30
2 a b 45
2 b a 70
2 b b 100希望得到以下结果
类型A 类型B 数量
a a -10
a b 25
b a 40
b b 100也就是求时间段2里面对时间段1的增量
SELECT 类型A,类型B,數量-(Select s.數量 from t s Where s.時間段=1 and s.類型A=t.類型A and s.類型B=t.類型B ) from t where 時間段=2
类型A = ISNULL(A.类型A, B.类型A),
类型B = ISNULL(A.类型B, B.类型B),
数量 = ISNULL(B.数量, 0) - ISNULL(A.数量, 0)
FROM 表A A
FULL JOIN 表B B
ON A.时间段 = 1
AND B.时间段 = 2
AND A.类型A = B.类型A
AND A.类型B = B.类型B
INSERT @a SELECT 1, 'a', 'a', 10
UNION ALL SELECT 1 ,'a', 'b', 20
UNION ALL SELECT 1 ,'b', 'a', 30
UNION ALL SELECT 2 ,'a', 'b', 45
UNION ALL SELECT 2 ,'b', 'a', 70
UNION ALL SELECT 2 ,'b', 'b', 100 select aa.la,aa.lB,数量=sum(case when sj=1 then -isnull(sl,0) else isnull(sl,0) end)
from
(select distinct lA,lB from @A)aa
Left Join
@A bb
on aa.lA=bb.lA and aa.lB=bb.lb
group by aa.lA,aa.lB--result
/*la lB 数量
-------------------- -------------------- -----------
a a -10
b a 40
a b 25
b b 100 (所影响的行数为 4 行)
*/
declare @tb table(时间段 int, 类型A varchar(10), 类型B varchar(10), 数量 int)
insert @tb
select 1, 'a', 'a' , 10 union all
select 1, 'a', 'b' , 20 union all
select 1, 'b', 'a' , 30 union all
select 2, 'a' , 'b' , 45 union all
select 2, 'b' , 'a' , 70 union all
select 2, 'b' , 'b' , 100 select 类型A = ISNULL(A.类型A, B.类型A),
类型B = ISNULL(A.类型B, B.类型B),
数量 = ISNULL(B.数量, 0) - ISNULL(A.数量, 0)
from
(select * from @tb where 时间段=1) a full join
(select * from @tb where 时间段=2) b on a.类型A=b.类型A and a.类型B=b.类型B
order by 类型A,类型B
/*
类型A 类型B 数量
---------- ---------- -----------
a a -10
a b 25
b a 40
b b 100
*/
select 类型A,类型B
,数量=sum(case 时间段 when 1 then -数量 when 2 then 数量 else 0 end)
group by 类型A,类型B
FROM
(SELECT DISTINCT S_A,S_B FROM TABLE1 A)A
LEFT JOIN (SELECT S_A ,S_B,SUM(QTY)QTY FROM TABLE1
WHERE SNO=2
GROUP BY S_A,S_B)B ON (A.S_A=B.S_A AND A.S_B=B.S_B)LEFT JOIN
(SELECT S_A ,S_B,SUM(QTY)QTY FROM TABLE1
WHERE SNO=1
GROUP BY S_A,S_B)C ON (A.S_A=C.S_A AND A.S_B=C.S_B)
DECLARE @a tABLE(sj int,la varchar(20),lb varchar(20),sl int)
INSERT @a SELECT 1, 'a', 'a', 10
UNION ALL SELECT 1 ,'a', 'b', 20
UNION ALL SELECT 1 ,'b', 'a', 30
UNION ALL SELECT 2 ,'a', 'b', 45
UNION ALL SELECT 2 ,'b', 'a', 70
UNION ALL SELECT 2 ,'b', 'b', 100 select la,lb,sum(case when sj=1 then -sl else isnull(sl,0) end) from @a group by la,lb
a a -10
b a 40
a b 25
b b 100
FROM
(SELECT S_A ,S_B,SUM(QTY)QTY FROM TABLE1
WHERE SNO=2
GROUP BY S_A,S_B)B FULL JOIN
(SELECT S_A ,S_B,SUM(QTY)QTY FROM TABLE1
WHERE SNO=1
GROUP BY S_A,S_B)C ON (B.S_A=C.S_A AND B.S_B=C.S_B)
使用的是sybase iq,每列都有FP索引。
insert tb
select 1, 'a', 'a' , 10 union all
select 1, 'a', 'b' , 20 union all
select 1, 'b', 'a' , 30 union all
select 2, 'a' , 'b' , 45 union all
select 2, 'b' , 'a' , 70 union all
select 2, 'b' , 'b' , 100
select 类型A,类型B
,数量=sum(case 时间段 when 1 then -数量 when 2 then 数量 else 0 end)
from tb
group by 类型A,类型B
order by 类型A,类型B
/*
---------------
a a -10
b a 40
a b 25
b b 100
*/