数据查询问题.请教大家,谢谢

[daqi2010]

表chitty
chitty_id chitty_sort(类型) chitty_money chitty_time
1 1 500.00 2009-4-4
2 1 200.00 2009-5-5
3 3 100.00 2009-5-6
4 3 300.00 2006-5-5
..
表item(明细)
id chitty_id vchitty_id(明细chitty_id)
1 3 1
2 4 2
..
问题:取得本月chitty_sort=3时的数据后,根据vchitty_id得到所对应金额
如本例:当chitty_sort=3时,从表chitty中知道chitty_id为3和4,然后对应明细表中的明细chitty_id为1和2,最后得到所对应的金额为500.00+200.00=700.00
谢谢大家了

[malin2670372]

select sum(chitty_money) as total from chitty  where chitty_id in(select a.id from Item left outer join chity  b  on a.chitty_id=b.Chitty_id where b.chitty_sort=3))

[wxg22526451]

--Code
select 金额=sum(chitty_money) from @chitty  
where [chitty_id] in 
(select vchitty_id from @chitty a join @Item b on  a.chitty_id=b.Chitty_id and a.chitty_sort=3 )
 
--Result
/*
金额
---------------------------------------
700.00
*/

[you_tube]

selec sum(chitty .chitty_money) from chitty 
left join item
on chitty .chitty_id   = item.chitty_id
where chitty_sort = 3

[zoom8]

declare @chitty table(chitty_id int,chitty_sort int,chitty_money money,chitty_date datetime)
insert @chitty select 1,1,500.00,'2009-4-4'
union all select 2,1,200.00,'2009-5-6'
union all select 3,3,100.00,'2009-5-6'
union all select 4,3,300.00,'2009-5-6'
declare @item table(id int,chitty_id int,vchitty_id int)
insert @item select 1,3,1
union all select 2,4,2
--
select sum(chitty_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 ))

[SQL77]

create  table tbtest(chitty_id int,chitty_sort int,chitty_money money,chitty_date datetime)
insert tbtest 
select 1,1,500.00,'2009-4-4'
union all select 2,1,200.00,'2009-5-6'
union all select 3,3,100.00,'2009-5-6'
union all select 4,3,300.00,'2009-5-6'

create  table tbtest1(id int,chitty_id int,vchitty_id int)
insert tbtest1
 select 1,3,1 union all 
 select 2,4,2

--select * from tbtest
--select * from tbtest1

select sum(chitty_money) 总计 from tbtest 
where chitty_id 
in
(select vchitty_id from tbtest1 
inner join 
(select chitty_id from tbtest where chitty_sort=3) as t
on tbtest1.chitty_id=t.chitty_id)

700.0000

[ks_reny]

[Quote=引用楼主 daqi2010 的帖子:]
表chitty
chitty_id chitty_sort(类型) chitty_money chitty_time
1 1 500.00 2009-4-4
2 1 200.00 2009-5-5
3 3 100.00 2009-5-6
4 3 300.00 2006-5-5
..
表item(明细)
id chitty_id vchitty_id(明细chitty_id)
1 …
[/Quote]

select sum（chitty_money）from chitty  where chitty_id in（select vchitty_id from item ，chitty  where item.chitty_id=chitty.chitty_id and  chitty.chitty_sort=3）

[daqi2010]

谢谢大家.还有一个小问题,如果
SET @SQL ='select top 15
a.corp_id,a.corp_name,
select 金额=sum(chitty_money) from @chitty
where [chitty_id] in
(select vchitty_id from @chitty a join @Item b on a.chitty_id=b.Chitty_id and a.chitty_sort=3 )
from corp a left join ..... GROUP by a.corp_id,a.corp_name

要么所有的金额都一样OR提示"不能对包含聚合或子查询的表达式执行聚合函数"