主页

求sql语法?

csover8 2010-07-02 10:17:06

有表a,表b,表NonP

表a的大概架构是:

id babyid rdate IsB amount
1 26 2010-06 1 500
2 58 2010-06 0 200
3 26 2010-06 1 25

表b的大概架构是:
id aid bamount
1 1 250
2 1 200
3 3 25

表NonP的大概架构是:
babyid amount NonDate

如何通过表a,表b两个表的记录,通过SQL语法,得到每个对应babyid的amount的值后,插入到NonP表中去?
最近NonP的记录是这样的:

babyid amount NonDate
26 75 2010-06
58 0 2010-06

...全文

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

挨踢直男 2010-07-02

表设计有点问题或者你问题没表达清楚

回复赞

bbsxun 2010-07-02

select t2.babyid,sum(t2.amount) from (select t1.babyid,t1.amount-t.bamount as amount from a t1 left join (select aid,sum(bamount) from b group by aid) t on a.id=b.aid) t2 group by t2.babyid

回复赞

vincentmax 2010-07-02

insert into NonP values = select a.babyid, sum(a.amount - b.bamount), a.rdate from a, b where a.id=b.id group by a.babyid

可能不同数据库具体语法不太一样，
但是逻辑就是这样了。
也可以利用中间表来做。

回复赞

pgfpmnui 2010-07-02

SELECT t1.BabyID ,
t1.Amount - ISNULL(t2.Amount, 0)
FROM ( SELECT BabyID ,
SUM(Amount) AS Amount
FROM a
GROUP BY BabyID
) AS t1
LEFT JOIN ( SELECT a.BabyID ,
SUM(b.Amount) AS Amount
FROM a
INNER JOIN b ON a.ID = b.AID
GROUP BY a.BabyID
) AS t2 ON t1.BabyID = t2.BabyID

回复赞

pgfpmnui 2010-07-02

逻辑好像不太对或表结构设计得有点问题

回复赞