有三个表.
a表
字段:stcd , stnm
例如:
1,q1
2,w2b表
字段
stcd ,idtm(datatime),accp(int)
例如:
1,2005-06-01,20
1,2005-06-01,30
2,2005-06-01,50
2,2005-06-01,1
c表
字段
stcd,MYAVP(int)
例如:
1,40
1,40
2,2
2,50现在要求得到下面的结果,
stcd,stnm,idtm,accp,MYAVP1 ,q1 ,2005-06-01,50,80
2 ,w2 ,2005-06-01,51,52对上面的accp.MYAVP按stcd分组求和!
求该sql 语句.
谢谢
a表
字段:stcd , stnm
例如:
1,q1
2,w2b表
字段
stcd ,idtm(datatime),accp(int)
例如:
1,2005-06-01,20
1,2005-06-01,30
2,2005-06-01,50
2,2005-06-01,1
c表
字段
stcd,MYAVP(int)
例如:
1,40
1,40
2,2
2,50现在要求得到下面的结果,
stcd,stnm,idtm,accp,MYAVP1 ,q1 ,2005-06-01,50,80
2 ,w2 ,2005-06-01,51,52对上面的accp.MYAVP按stcd分组求和!
求该sql 语句.
谢谢
a.*,
b.idtm,
sum(b.accp) as accp,
(select sum(MYAVP) from c where stcd=a.stcd) as MYAVP
from
a,b
where
a.stcd=b.stcd
group by
a.stcd,a.stnm,b.idtm
select A.stcd, min(B.idtm) as idtm,sum(B.accp) as accp,sum(C.myavp) as myavp
from A
inner join B on A.stcd=B.stcd
inner join C on A.stcd=C.stcd
group by A.stcd, A.stnm
order by A.stcd, A.stnm
where A.stcd=B.stcd and A.stcd=C.stcd
order by A.stcd
http://community.csdn.net/Expert/topic/5080/5080966.xml?temp=.8571436