有人能写出这条SQL语句吗？谢谢！(100分)

已知以下四个表

·材料数据表（T1）：此表以编号为索引，且没有重复记录

编号 品种 厂商
1 AAA aa
2 BBB bb
3 CCC cc
4 DDD dd

·月初数量表（T2）：此表以编号为索引，且没有重复记录

编号 月初库存
1 50
2 40
3 10
4 15

·材料入库明细表（T3）：此表以编号为索引，有重复记录

编号 入库量
2 40
1 15
3 60
4 50
2 40
3 35
1 15
2 80

·材料领用明细表（T4）：此表以编号为索引，有重复记录

编号 领用量
4 7
2 5
3 4
3 3
4 2
1 5
2 6
2 7

求以下结果表的SQL的语句？

编号 品种 厂商 月初库存 入库量 领用量 库存量
1 AAA aa 50 30 5 75
2 BBB bb 40 160 18 182
3 CCC cc 10 95 7 98
4 DDD dd 15 50 9 56

select t1.id,t1.AAA,t1.aa,sum(t2.yckc),sum(t3.rkl),sum(t4.lyl),sum(t2.yckc)+sum(t3.rkl)-sum(t4.lyl) as kcl
from t1,t2,t3,t4
where t1.id=t2.id and t1.id=t3.id and t1.id=t4.id
group by t1.id,t1.AAA,t1.aa
order by t1.id,t1.AAA,t1.aa

试试以下的方法行不行：
select 编号，品种，厂商，月初库存， 入库量，领用量，库存量 from t1,t2,t3,t4
where ti.编号=t2.编号 and t1.编号=t3.编号 and t1.编号=t4.编号

我做了一个实验，成功！

select c.code ,c.name,c.provider ,sum(yuechu.value) as 月初,sum(ad.value) as 入库,sum(out.value) as 出库,
(sum(yuechu.value)+sum(ad.value)-sum(out.value)) as 库存
from 材料表 c ,
(select code,sum(value) as value from 领用名细表 group by code ) out,
(select code,sum(value) as value from 入库表 group by code) ad,
(select code,sum(value) as value from 月初数据 group by code) yuechu
where c.code = out.code and c.code=yuechu.code and c.code=ad.code
group by c.code
order by c.code
有几个字段换了。

select T1.编号,T1.品种,T1.厂商,T2.月初库存,sum(T3.入库量),sum(T4.领用量),T2.月初库存+sum(T3.入库量)-sum(T4.领用量) as 库存量
from T1,T2,T3,T4
where T1.编号=T2.编号 and T2.编号=T3.编号 and T3.编号=T4.编号

select c.code ,c.name,c.provider ,sum(yuechu.value) as 月初,sum(ad.value) as 入库,sum(out.value) as 出库,
(sum(yuechu.value)+sum(ad.value)-sum(out.value)) as 库存
from 材料表 c ,
(select code,sum(value) as value from 领用名细表 group by code ) out,
(select code,sum(value) as value from 入库表 group by code) ad,
(select code,sum(value) as value from 月初数据 group by code) yuechu
where c.code *= out.code and c.code*=yuechu.code and c.code*=ad.code
group by c.code
order by c.code

select t1.编号,t1.品种,t1.厂商,sum(t2.月初库存)as 月初库存,sum(t3.入库量)as 入库量,sum(t4.领用量)as领用量,
库存量=月初库存+入库量-领用量
from t1,t2.t3.t4
where t1.id=t2.id and t1.id=t3.id and t1.id=t4.id
group by t1.编号,t1.品种,t1.厂商

SELECT t1.code ,t1.name,t1.provider ,t2.月初库存 月初,t3.IN_VAL 入库,t4.OUT_VAL 出库,
t2.月初库存+t3.IN_VAL-t4.OUT_VAL 库存
FROM t1 , t2 ,
(SELECT code,SUM(value) IN_VAL FROM t3 GROUP BY code ) t3,
(SELECT code,SUM(value) OUT_VAL FROM t4 GROUP BY code) t4,
WHERE t1.code = t2.code AND t1.code=t3.code(+) AND t1.code=t4.code(+)
ORDER BY c.code

Sql="select T1.bianhao,pingzhong,changshang,yckc,
T_rk.count,T_ly.count ,(yckc+T_rk.count-T_ly.count)
from T1,T2 ,
( select bianhao,sum(rkl) as count from T3 Group by bianhao ) T_rk,
( Select bianhao,sum(lyl) as count from T4 Group By bianhao ) T_ly
where T1.bianhao=T2.bianhao
and T_rk.bianhao=T1.bianhao and T_ly.bianhao=T1.bianhao
order by T1.bianhao"
我后台的数据库是ACCESS,在网页中试过可以！

比较简单的。
但我做的不好。cacheway 做的方法最完全。
我做的有点多此一举。：-（。

老板，结帐！

用一个视图实现了非常容易感兴趣请回mail