表A(消费记录) 字段有:id(主键),money(金额),recordDate(记录时间),bid(主B表关联ID)
表B(人员信息)字段有:id(主键),name(姓名),age(年龄),A表中的信息是每一次的消费记录,B表中是每个人的基本信息现在想用SQL 查出每个人的消费总数, 查出:B.name ,B.age ,sum(A.money) ,查询条件为 where B.name like '%%'
表B(人员信息)字段有:id(主键),name(姓名),age(年龄),A表中的信息是每一次的消费记录,B表中是每个人的基本信息现在想用SQL 查出每个人的消费总数, 查出:B.name ,B.age ,sum(A.money) ,查询条件为 where B.name like '%%'
from a,b
where a.bid=b.id
and b.name like '%%'
group by b.name,b.age
from a inner join b on a.bid=b.id
where b.name like '%%'
group by b.name,b.age
SELECT B.NAME,B.AGE,NVL(C.M,0)
FROM B
LEFT JOIN (SELECT A.BID,SUM(MONEY) FROM A GROUP BY A.BID) C
ON B.ID = C.BID;
FROM B
LEFT JOIN (SELECT A.BID,SUM(MONEY) M FROM A GROUP BY A.BID) C
ON B.ID = C.BID;
from A, B
where A.bid = B.id
and B.name like '%%'
group by B.name, B.age
select b.id,b.name,b.age,sum(a.money)
from a,b
where a.bid=b.id
and b.name like '%%'
group by b.bid,b.name,b.age
order by b.bid,b.name,b.age
select B.name ,B.age
(seelct sum(A.money) from 表A A where B.id=A.bid) as sum_money
from B表 B
where B.name like '%%'
select B.name ,B.age
(select sum(A.money) from 表A A where B.id=A.bid) as sum_money
from B表 B
where B.name like '%%'
from B,
(select A.bid ,sum(A.money) money
from A group by A.bid) C
where B.id = C.bid(+)
select B.name,
B.age,
(seelct sum(A.money) from A where A.bid= B.id) as sum_money
from B
where B.name like '%%'
select B.name,
B.age,
(seelct sum(NVL(A.money,0)) from A where A.bid= B.id) as sum_money
from B
where B.name like '%%'
参考了上面的所有人的,你试试看,可不可以
select b.name,b.age,sum(nvl(a.money,0)) from b left join a on a.id = b.id
where b.name like '%%'
group by b.name,b.age