关于同一个表的两类不同数据相减的问题---在线等啊。求求高手了
表内容：
tank_id oil_id Oil_Mass Tank_status
 101 1 100 0
 101 1 500 1
 102 1 100 0
 102 1 400 1
 103 2 50 0
 103 2 200 1
 104 3 100 0
 104 3 300 1

说明：
tank_id 与 oil_id 为主键。Tank_status字段中“0”与“1”为一组相关数据

完成功能：
查询Tank_status为“1”的减去Tank_status为“0”的所有记录。

请问如何实现。

谢谢在
在线等


------解决方案--------------------


---这样?

select 
tank_id, 
oil_id,
sum(case when Tank_status=1 then Oil_Mass else Oil_Mass * (-1) end) as 合计
from 表
group by tank_id, oil_id
------解决方案--------------------
select
isnull(a.tank_id,b.tank_id) as tank_id ,
isnull(a.oil_id ,b.oil_id ) as oil_id ,
isnull(a.Oil_Mass,0)-isnull(b.Oil_Mass,0) as Oil_Mass
from
(select * from 表 where Tank_status=1) a
full outer join
(select * from 表 where Tank_status=0) b
on
a.tank_id=b.tank_id and a.oil_id=b.oil_id
order by
tank_id,oil_id
------解决方案--------------------


create table T (tank_id int, oil_id int, Oil_Mass int,Tank_status int)


insert into T select 101, 1, 100, 0 
insert into T select 101, 1, 500, 1 
insert into T select 102, 1, 100, 0 
insert into T select 102, 1, 400, 1 
insert into T select 103, 2, 50, 0 
insert into T select 103, 2, 200, 1 
insert into T select 104, 3, 100, 0 
insert into T select 104, 3, 300, 1 

--你要的SQL语句
select 
tank_id, 
oil_id,
sum(case when Tank_status=1 then Oil_Mass else Oil_Mass * (-1) end) as 合计
from T
group by tank_id, oil_id


drop table T


/*

--结果

tank_id oil_id 合计
---------------------------------------
101 1 400
102 1 300
103 2 150
104 3 200


*/
------解决方案--------------------
select tank_id,oil_id,Oil_Mass=sum(case Tank_status when 1 then Oil_Mass else - Oil_Mass end) from tb group by tank_id,oil_id
------解决方案--------------------
create table tb(tank_id int,oil_id int,Oil_Mass int,Tank_status int)
insert into tb values(101, 1, 100, 0 )
insert into tb values(101, 1, 500, 1 )
insert into tb values(102, 1, 100, 0 )
insert into tb values(102, 1, 400, 1 )
insert into tb values(103, 2, 50 , 0 )
insert into tb values(103, 2, 200, 1 )
insert into tb values(104, 3, 100, 0 )
insert into tb values(104, 3, 300, 1 )
select tank_id,oil_id,Oil_Mass=sum(case Tank_status when 1 then Oil_Mass else - Oil_Mass end) from tb group by tank_id,oil_id
drop table tb
/*
tank_id oil_id Oil_Mass
----------- ----------- ----------- 
101 1 400
102 1 300
103 2 150
104 3 200
*/
------解决方案--------------------

create table T (tank_id int, oil_id int, Oil_Mass int,Tank_status int)