关于同一个表的两类不同数据相减的问题---在线等啊。求求高手了

[smallfile]

表内容：
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”的所有记录。

请问如何实现。

谢谢在
在线等

[OracleRoob]

---这样?

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

[OracleRoob]

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


*/

[dawugui]

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

[dawugui]

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
*/

[OracleRoob]

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语句

--方式1：
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



--方式2：
select tank_id, oil_id ,sum(Oil_Mass) as 合计
from
(
select tank_id, oil_id ,Oil_Mass from T where Tank_status=1
union all
select tank_id, oil_id ,-1*Oil_Mass as Oil_Mass from T where Tank_status=0
) as A
group by tank_id, oil_id


--方式3：如二楼的full join方式

drop table T


/*

--结果

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


*/

[smallfile]

搞定
我用的是6搂所说的第二种方式
谢谢各位了
马上给分