有A、B两张表,如下:A
id number price
1001 10 100.0
1002 5 300.0
B
id eid cash
2001 1001 500.0
2002 1001 300.0通过一个sql语句查询出如下结果,A表有多少条记录,结果集就有几条记录A.id amount in left
1001 1000(number*price) 800 200(amount-in)
1002 1500 0 1500
id number price
1001 10 100.0
1002 5 300.0
B
id eid cash
2001 1001 500.0
2002 1001 300.0通过一个sql语句查询出如下结果,A表有多少条记录,结果集就有几条记录A.id amount in left
1001 1000(number*price) 800 200(amount-in)
1002 1500 0 1500
from a
left join
(select eid,sum(cash) as in from b group by eid) b
from A left join (select eid,sum(cash) as sCash from B group by eid) c on a.id=c.eid
[align=center]==== 思想重于技巧 ====
[/align]
from a
left join
(select eid,sum(cash) as in from b group by eid) b
from a
left join
(select eid,sum(cash) as in from b group by eid) b
on a.id=B.eid
或参考:
http://www.csdn.net/help/over.asp
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
=============================================================================
问题解决,请及时结贴。
正确结贴方法:
管理帖子-->给分-->输入密码-->结贴
[align=center]==== 思想重于技巧 ====
[/align]
from A left join (select eid,sum(cash) as sCash from B group by eid) c on a.id=c.eid
where A.number*A.price-IFNULL(c.sCash,0) >0;
[align=center]==== 思想重于技巧 ====
[/align]