如何分别汇总金额,请各位大哥指点.谢谢了.

[daqi2010]

表chitty
chitty_id chitty_sort(类型) chitty_money chitty_time corp_id
1 1 500.00 2009-4-4 1
2 1 400.00 2009-5-5 2
3 2 300.00 2009-5-6 1
4 2 200.00 2006-5-5 2
5 3 100.00 2009-5-6 1
6 3 150.00 2006-5-5 2
..
表item(明细)
id chitty_id vchitty_id(明细chitty_id)
1 5 1
2 6 2
3 5 3
4 6 4
..
表corp(客户表)
corp_id corp_name corp_money(初使金额)
1 上海中华 100.00
2 天津** 200.00
问题:取得本月chitty_sort=3时的数据后,根据vchitty_id得到所对应金额
如本例:当chitty_sort=3时,从表chitty中知道chitty_id为5和6,然后对应明细表中的明细chitty_id为1,2,3,4(既明细表中的vchitty_id字段的值),最后得到

corp_id corp_name corp_money money
1 上海中华 100.00 800.00=500.00+300.00
2 天津** 200.00 600.00=400.00+200.00

谢谢大家了

[ks_reny]

 select a.*,sum(b.chitty_money) as money from corp a
( select corp_id ,chitty_money   from  chitty  a
 (select b.vchitty_id  from chitty  a，item b where a.chitty_id=b.chitty_id and a.chitty_sort=3) b
 where a.chitty_id=b.vchitty_id ) b
where a.corp_id=b.corp_id 
group by a.corp_id,a.corp_name,a.corp_money      

[SQL77]

create table [chitty]([chitty_id] int,[chitty_sort] int,[chitty_money] numeric(5,2),[chitty_time] datetime,[corp_id] int)
insert [chitty]
select 1,1,500.00,'2009-4-4',1 union all
select 2,1,400.00,'2009-5-5',2 union all
select 3,2,300.00,'2009-5-6',1 union all
select 4,2,200.00,'2006-5-5',2 union all
select 5,3,100.00,'2009-5-6',1 union all
select 6,3,150.00,'2006-5-5',2

if object_id('[item]') is not null drop table [item]
go
create table [item]([id] int,[chitty_id] int,[vchitty_id] int)
insert [item]
select 1,5,1 union all
select 2,6,2 union all
select 3,5,3 union all
select 4,6,4

if object_id('[corp]') is not null drop table [corp]
go
create table [corp]([corp_id] int,[corp_name] varchar(8),[corp_money] numeric(5,2))
insert [corp]
select 1,'上海中华',100.00 union all
select 2,'天津**',200.00




SELECT corp.*,[MONEY] FROM corp,
      (SELECT corp_id,SUM(chitty_money)[MONEY] FROM chitty 
              WHERE chitty_id IN(SELECT vchitty_id FROM item WHERE chitty_id IN 
              (SELECT chitty_id FROM chitty WHERE chitty_sort=3))
      GROUP BY corp_id) AS T
WHERE corp.corp_id=T.corp_id

corp_id corp_name corp_money MONEY
----------- --------- ---------- ----------------------------------------
1 上海中华 100.00 800.00
2 天津** 200.00 600.00

（所影响的行数为 2 行）

[等不到来世]

if object_id('[chitty]') is not null drop table [chitty]
go
create table [chitty]([chitty_id] varchar(2),[chitty_sort] int,[chitty_money] numeric(5,2),[chitty_time] datetime,[corp_id] int)
insert [chitty]
select '1',1,500.00,'2009-4-4',1 union all
select '2',1,400.00,'2009-5-5',2 union all
select '3',2,300.00,'2009-5-6',1 union all
select '4',2,200.00,'2006-5-5',2 union all
select '5',3,100.00,'2009-5-6',1 union all
select '6',3,150.00,'2006-5-5',2
go
if object_id('[item]') is not null drop table [item]
go
create table [item]([id] varchar(2),[chitty_id] int,[vchitty_id] int)
insert [item]
select '1',5,1 union all
select '2',6,2 union all
select '3',5,3 union all
select '4',6,4 
go
if object_id('[corp]') is not null drop table [corp]
go
create table [corp]([corp_id] int,[corp_name] varchar(8),[corp_money] numeric(5,2))
insert [corp]
select 1,'上海中华',100.00 union all
select 2,'天津**',200.00
go
--select * from [chitty]
--select * from [item]
--select * from [corp]



select d.corp_id,d.corp_name,d.corp_money,money=sum(c.chitty_money)
from chitty a
join item b on a.chitty_id=b.chitty_id
join chitty c on b.vchitty_id=c.chitty_id
join corp d on c.corp_id=d.corp_id
where a.chitty_sort=3
group by d.corp_id,d.corp_name,d.corp_money
/*
corp_id     corp_name corp_money                              money
----------- --------- --------------------------------------- ---------------------------------------
1           上海中华      100.00                                  800.00
2           天津**      200.00                                  600.00

(2 行受影响)
*/

[ks_reny]

[Quote=引用 3 楼 daqi2010 的回复:]
非常谢谢大家,不过还是有问题
冷月的测试不出来.
百年树人的如果表Chitty有其他记录.则统计结果将所有的全部统计了
[/Quote]
不好意思，我少写了两个逗号，现在改为下面的，我用了SQL77的数据。

select a.*,sum(b.chitty_money) as money from corp a,
( select corp_id ,chitty_money   from  chitty  a,
 (select b.vchitty_id  from chitty  a,item b where a.chitty_id=b.chitty_id and a.chitty_sort=3) b
 where a.chitty_id=b.vchitty_id ) b
where a.corp_id=b.corp_id 
group by a.corp_id,a.corp_name,a.corp_money  
--------------------------------------------------------
1	上海中华	100.00	800.00
2	天津**	200.00	600.00

[daqi2010]

非常谢谢大家,不过还是有问题
冷月的测试不出来.
百年树人的如果表Chitty有其他记录.则统计结果将所有的全部统计了

[百年树人]

---测试数据---
if object_id('[chitty]') is not null drop table [chitty]
go
create table [chitty]([chitty_id] int,[chitty_sort] int,[chitty_money] numeric(5,2),[chitty_time] datetime,[corp_id] int)
insert [chitty]
select 1,1,500.00,'2009-4-4',1 union all
select 2,1,400.00,'2009-5-5',2 union all
select 3,2,300.00,'2009-5-6',1 union all
select 4,2,200.00,'2006-5-5',2 union all
select 5,3,100.00,'2009-5-6',1 union all
select 6,3,150.00,'2006-5-5',2
if object_id('[item]') is not null drop table [item]
go
create table [item]([id] int,[chitty_id] int,[vchitty_id] int)
insert [item]
select 1,5,1 union all
select 2,6,2 union all
select 3,5,3 union all
select 4,6,4
if object_id('[corp]') is not null drop table [corp]
go
create table [corp]([corp_id] int,[corp_name] varchar(8),[corp_money] numeric(5,2))
insert [corp]
select 1,'上海中华',100.00 union all
select 2,'天津**',200.00
 
---查询---
select
  c.corp_id,
  c.corp_name,
  c.corp_money,
  sum(d.chitty_money) as [money]
from 
  [chitty] a,
  [item] b,
  [corp] c,
  [chitty] d
where
  a.chitty_sort=3
and
  a.chitty_id=b.chitty_id
and
  b.vchitty_id=d.chitty_id
and 
  a.corp_id=c.corp_id
group by
  c.corp_id,
  c.corp_name,
  c.corp_money

---结果---
corp_id     corp_name corp_money money                                    
----------- --------- ---------- ---------------------------------------- 
1           上海中华      100.00     800.00
2           天津**      200.00     600.00

（所影响的行数为 2 行）