如TableA
id money
1 1
2 1
1 1表TableB
id
1
2
3最后得出结果
1 2
2 1
3 0
即sum(money),TableA中没有id3,sum后的结果为0,但TableB中有id3,也希望结果集能显示id3请问怎么做啊,在线等。。
id money
1 1
2 1
1 1表TableB
id
1
2
3最后得出结果
1 2
2 1
3 0
即sum(money),TableA中没有id3,sum后的结果为0,但TableB中有id3,也希望结果集能显示id3请问怎么做啊,在线等。。
sum(nvl(a.money,0))
from tableB b
left join tableA a on (a.id=b.id)
group by b.id试试这个
c.money
from tableB b
left join
(select id,sum(money) as meney
from a
group by id
) c
on (c.id=b.id)
from agent a left join card c on (c.agent_id=a.agent_id)
where c.charge_date>=to_date('2007-11-27','yyyy-mm-dd')
group by a.agent_id这样不行,agent里的id不能全部显示出来
create table a
(
id number,
money number
);insert into a
select 1,1 from dual
union all
select 2,1 from dual
union all
select 1,1 from dual;drop table b;
create table b
(
id number
);
insert into b
select 1 from dual
union all
select 2 from dual
union all
select 3 from dual;select b.id,
sum(nvl(a.money,0))
from B b
left join A a on (a.id=b.id)
group by b.id; ID SUM(NVL(A.MONEY,0))
---------- -------------------
1 2
2 1
3 0