表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怎么写?
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怎么写?
from a,b
where a.id=b.id
group by a.id,a.name,a.flag
left join (select id,sum(money) sm from b group by id) b1
on b1.id=a.id
left join (select id,decode(sum(flag),0,0,1) sf from c group by id) c1
on c1.id=a.id
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 0SQL>
SQL> SELECT A.ID,
2 A.NAME,
3 MAX(MONEY) "MONEY",
4 MAX(FLAG) "FLAG"
5 FROM A,
6 (
7 SELECT ID,
8 SUM(MONEY) "MONEY"
9 FROM B
10 GROUP BY ID
11 )BB,
12 C
13 WHERE A.ID = BB.ID
14 AND BB.ID = C.ID
15 GROUP BY A.ID,A.NAME
16 ; ID NAME MONEY FLAG
---------- ---- ---------- ----------
1 AA 80 1
2 BB 300 0SQL>
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;