超大难度SQL语句. (85分)

表A
gysbh gysname (gysbh主键)
1 张
2 李

表B
gysbh jhdh (gysbh是表A的外键, jhdh是表B的主键,)
1 2002-01-01
2 2002-01-05
1 2002-01-02
1 2002-01-03

表C
jhdh sl dj (jhdh是表B的外键)
2002-01-01 1 2
2002-01-01 3 5
2002-01-05 1 10
2002-01-02 1 10
2002-01-03 3 10

表D
gysbh ytmoney THDH (gysb的外键,THDH是主键)
1 30 001
2 40 002

最终结果表E是这样的.

gysbh gysname Cmoney
1 张 57-表D.ytmoney(30)
2 李 10-40


Cmoney/这个字段是一个 as Cmoney的字段　
select B.JHDH,B.gysbh,Sum(C.money) AS Cmoney,A.gysname from jhdb B,
(select jhdh,sum(dj*sl) as money from jhmxb group by jhdh) C,
where B.jhdh=C.jhdh
GROUP BY B.GYSBH
它的值是表 Sum(C.money) as Cmoney-D.ytmoney 来的.

这条语句我写的有问题，

select gysbh,gysname,
(select sum(je)
from (select gysbh,(select sum(sl*dj) as je
from 表C where jhdh=BBB.jhdh) as je
from 表B BBB) KKK
where gysbh=AAA.gysbh)-
(select ytmoney from 表D where gysbh=AAA.gysbh) as ytmoney
from 表A AAA

在MSSQL中验证过

select c.gysbm,c.jhmoney-isnull(d.ytmoney,0),a.gysmc
from 表A a,
(select t1.gysbm,jhmoney=sum(t2.sl*t2.dj)
from 表B t1,表C t2
where t1.jhdh=t2.jhdh
group by t1.gysbm) c,
(select gysbm,ytmoney=sum(ytmoney) from 表D
group by gysbm) d
where a.gysbm*=c.gysbm and a.gysbm*=d.gysbm