表A id name 表C id flag
1 aa 1 0
2 bb 1 1
表B id money 1 0
1 50 2 0
1 20 2 0
1 10
2 100
2 200
表A、表B和表C的id是关联的，一对多关系，表C中的flag有一个是1,结果就是1
我想要的结果是 id name sum(money) flag
1 aa 80 1
2 bb 300 0
sql怎么写？
------解决方案--------------------
- SQL code
select a.id,a.name,sum(b.name) ,a.flag from a,bwhere a.id=b.idgroup by a.id,a.name,a.flag
------解决方案--------------------
- SQL code
select a.id,a.name,b1.sm,c1.sf from a left join (select id,sum(money) sm from b group by id) b1 on b1.id=a.idleft join (select id,decode(sum(flag),0,0,1) sf from c group by id) c1 on c1.id=a.id
------解决方案--------------------
TRY IT ..
- SQL code
SQL> SELECT A.ID,
2 A.NAME,
3 SUM(DISTINCT MONEY) "SUM_MONEY",
4 MAX(FLAG) "FLAG"
5 FROM A,
6 B,
7 C
8 WHERE A.ID = B.ID
9 AND B.ID = C.ID
10 GROUP BY A.ID,A.NAME
11 ;
ID NAME SUM_MONEY FLAG
---------- ---- ---------- ----------
1 AA 80 1
2 BB 300 0
SQL>
------解决方案--------------------
SQL> select a.*,g.money from (select distinct f.id,sum(f.money*c.flag) over(part
ition by f.id) money from (select distinct id,sum(money) over(partition by id
) money from b) f,c where c.id=f.id) g,a where g.id=a.id;