求一SQL(不同行列相减)

[liubocy]

表t_a中数据:
zh zhlb xh fkcode jdflag je
-------------------- ---- ----------- ---------- ------ ---------
1002 F 1 RMB 1 400.0000
1002 F 2 ## 0 200.0000
1003 F 1 RMB 1 200.0000
1003 F 2 MD 1 200.0000
1003 F 3 MD 1 200.0000
1003 F 4 ## 0 200.0000
1003 F 5 ## 0 200.0000
1003 F 6 ## 0 120.0000
1004 F 1 RMB 1 250.0000
1004 F 2 AC 1 250.0000
1004 F 3 MD 1 200.0000
1004 F 4 ## 0 100.0000
1004 F 5 ## 0 50.0000

根据要求得出如下数据:
zh zhlb fkcode je
-------------------- ---- ---------- ---------
1002 F RMB 200.0000
1003 F RMB 80.0000
1004 F AC 250.0000
1004 F MD 50.0000
1004 F RMB 250.0000

要求:
1. zh,zhlb相同的数据中,把jdflag为1的数据的je减jdflag为0的je
2. fkcode中MD的先减,RMB的后减

表t_a:
if exists( SELECT * FROM sysobjects WHERE xtype = 'u' and name = 't_a' )
drop table t_a
create table t_a (zh decimal,zhlb char(1),xh integer,fkcode char(10), jdflag char(1), je money default 0)
insert t_a
select 1002 ,'F',1,'RMB','1',400.0000 union all
select 1002 ,'F',2,'##','0', 200.0000 union all
select 1003 ,'F',1,'RMB','1',200.0000 union all
select 1003 ,'F',2,'MD','1', 200.0000 union all
select 1003 ,'F',3,'MD','1', 200.0000 union all
select 1003 ,'F',4,'##','0', 200.0000 union all
select 1003 ,'F',5,'##','0', 200.0000 union all
select 1003 ,'F',6,'##','0', 120.0000 union all
select 1004 ,'F',1,'RMB','1',250.0000 union all
select 1004 ,'F',2,'AC','1', 250.0000 union all
select 1004 ,'F',3,'MD','1', 200.0000 union all
select 1004 ,'F',4,'##','0', 100.0000 union all
select 1004 ,'F',5,'##','0', 50.0000

[zlp321002]

select
zh,
zhlb,
fkcode=max(fkcode),
je=(select sum(je) from t_a where zh=A.zh and jdflag=1)-(select sum(je) from t_a where zh=A.zh and jdflag=0)
from t_a A
group by zh,zhlb

[nononono]

fkcode为AC的怎么处理？在MD之后减？还有别的fkcode吗？是按照xh顺序减吗？

[liubocy]

zlp321002(她是我的唯一.) 的结果:
zh zhlb fkcode je
-------------------- ---- ---------- ---------------------
1002 F RMB 200.0000
1003 F RMB 80.0000
1004 F RMB 550.0000

正确结果:
zh zhlb fkcode je
-------------------- ---- ---------- ---------
1002 F RMB 200.0000
1003 F RMB 80.0000
1004 F AC 250.0000
1004 F MD 50.0000
1004 F RMB 250.0000

[ReViSion]

select zh,zhlb,fkcode=max(fkcode),je=sum(case jdflag when 1 then je else -je end) from t_a
group by zh,zhlb

哈哈， fkcode中MD的先减,RMB的后减
不是很明白

[zlp321002]

--第二个条件,我也没看明白.

[liubocy]

哈哈， fkcode中MD的先减,RMB的后减
不是很明白

同一zh,zhlb的数据fkcode中有MD存在的,先用MD减去jdflag为0的金额合计数,不够则用别的去减
优先次序:MD,AC,RMB

比如zh为1004,zhlb为F的几条数据中:
zh zhlb xh fkcode jdflag je
-------------------- ---- ----------- ---------- ------ ---------
1004 F 1 RMB 1 250.0000
1004 F 2 AC 1 250.0000
1004 F 3 MD 1 200.0000
1004 F 4 ## 0 100.0000
1004 F 5 ## 0 50.0000
先用MD的200减jdflag为0的合计数150,如果不够,就用AC的金额减,再不够就用RMB的金额减
得出结果:
zh zhlb fkcode je
-------------------- ---- ---------- ---------
1004 F AC 250.0000
1004 F MD 50.0000
1004 F RMB 250.0000

[liubocy]

比如zh为1003,zhlb为F的几条数据中:
zh zhlb xh fkcode jdflag je
-------------------- ---- ----------- ---------- ------ ---------
1003 F 1 RMB 1 200.0000
1003 F 2 MD 1 200.0000
1003 F 3 MD 1 200.0000
1003 F 4 ## 0 200.0000
1003 F 5 ## 0 200.0000
1003 F 6 ## 0 120.0000
先用MD的合计400减jdflag为0的合计数520,不够,就用RMB的金额减.在这里,MD的金额合计不够减,最后的结果1004,F的MD这行数据就没有.得出结果:
zh zhlb fkcode je
-------------------- ---- ---------- ---------
1003 F RMB 80.0000

[liubocy]

顶了

[ReViSion]

if exists( SELECT * FROM sysobjects WHERE xtype = 'u' and name = 't_a' )
drop table t_a
create table t_a (zh decimal,zhlb char(1),xh integer,fkcode char(10), jdflag char(1), je money default 0)
insert t_a
select 1002 ,'F',1,'RMB','1',400.0000 union all
select 1002 ,'F',2,'##','0', 200.0000 union all
select 1003 ,'F',1,'RMB','1',200.0000 union all
select 1003 ,'F',2,'MD','1', 200.0000 union all
select 1003 ,'F',3,'MD','1', 200.0000 union all
select 1003 ,'F',4,'##','0', 200.0000 union all
select 1003 ,'F',5,'##','0', 200.0000 union all
select 1003 ,'F',6,'##','0', 120.0000 union all
select 1004 ,'F',1,'RMB','1',250.0000 union all
select 1004 ,'F',2,'AC','1', 250.0000 union all
select 1004 ,'F',3,'MD','1', 200.0000 union all
select 1004 ,'F',4,'##','0', 100.0000 union all
select 1004 ,'F',5,'##','0', 50.0000

select a.zh,a.zhlb,fkcode,jd1=isnull(a.je,0),jd0=isnull(b.je,0) into #1 from
(select zh,zhlb,fkcode,je=sum(je) from t_a
where jdflag=1 group by zh,zhlb,fkcode)a
left join
(select zh,zhlb,je=sum(je) from t_a where jdflag=0
group by zh,zhlb) b
on a.zh=b.zh and a.zhlb=b.zhlb

--补全所有的记录
insert into #1
select distinct b.zh,b.zhlb,a.fkcode,isnull(c.jd1,0),b.jd0 from #1 b
left join (Select distinct fkcode from #1)a
on 1>0
left join #1 c
on c.zh=b.zh and c.zhlb=b.zhlb and c.fkcode=a.fkcode
where c.jd1 is null

update #1 set jd1=jd1-jd0
where fkcode='Md'

update #1 set jd1=a.jd1+b.jd1
from #1 a
inner join (select zh,zhlb,jd1 from #1 where fkcode='md' and jd1<0)b
on a.fkcode='AC' and a.zh=b.zh and a.zhlb=b.zhlb

update #1 set jd1=a.jd1+b.jd1
from #1 a
inner join (select zh,zhlb,jd1 from #1 where fkcode='AC' and jd1<0)b
on a.fkcode='RMB' and a.zh=b.zh and a.zhlb=b.zhlb

select zh,zhlb,fkcode,je=jd1 from #1
where jd1>0

[mislrb]

结贴了，也试试下面这个

if exists(select 1 from sysobjects where id=object_id(N'v_a') and xtype='V')
drop view v_a
go

create view v_a
as
select zh,zhlb,fkcode,newcode=case fkcode when 'MD' then 1 when 'AC' then 2 when 'RMB' then 3 else 0 end,je=sum(case jdflag when 1 then je else -je end)
from t_a
group by zh,zhlb,fkcode
go

select t.* from
(select a.zh,
a.zhlb,
a.fkcode,
je=case when (select sum(je) from v_a c where c.newcode<a.newcode and c.zh=a.zh and c.zhlb=a.zhlb)<=0
then (select sum(je) from v_a b where b.newcode<=a.newcode and b.zh=a.zh and b.zhlb=a.zhlb)
else je end
from v_a a
where a.je>0 and fkcode<>'##'
) t
where je>0
order by t.zh,t.zhlb,t.fkcode

drop view v_a