A表字段 A N
A1 7
A2 8
B表字段 B1,N1,N2,N3
A1,1 ,2 ,3
A1,4 ,5 ,6
A2,1 ,2 ,3
想得到:
A N
A1 28
A2 14即求和B再加A
A1 7
A2 8
B表字段 B1,N1,N2,N3
A1,1 ,2 ,3
A1,4 ,5 ,6
A2,1 ,2 ,3
想得到:
A N
A1 28
A2 14即求和B再加A
解决方案 »
- 请教一下:一个resource权限的用户能某些表不能访问?
- 两个表之间级联删除,为什么总提示我 违反完整约束条件 - 已找到子记录日志
- 可以发贴不能回复,如何处理?
- 求一SQL语句
- oracle的连接数不断增加,无法自动释放的问题?
- 有用过Developer2000的吗?谈谈看法,来者有分
- oracle数据库里是不是不能插入 繁体字——————
- 救命!
- 送分问题!!请问怎样在客户端用sqlplus连入远程数据库!save me!
- oracle8.0.5和oracle8.1.7不能exp ORACLE7.0.3K吗?一定给分,在线等
- 100 分问一个问题
- 寻求几本有关oracle8i备份和恢复的专题书籍
A N
A1 7
A2 8
B表字段
B1,N1,N2,N3
A1,1 ,2 ,3
A1,4 ,5 ,6
A2,1 ,2 ,3
想得到:
A N
A1 28
A2 14 即求和B再加A
, A.N+SUM(B.N1+B.N2+B.N3)
FROM A LEFT JOIN
B ON A.A=B.B1
, SUM(A.N+B.N1+B.N2+B.N3) AS N
FROM A LEFT JOIN
B ON A.A=B.B1
GROUP BY A.A
SQL> SELECT A.A,
2 SUM(B.N1+B.N2+B.N3)+MAX(A.N) AS N
3 FROM A,
4 B
5 WHERE A.A = B.B1
6 GROUP BY A.A;A N
-- ----------
A1 28
A2 14
SET N=N+ (
SELECT SUM(B.N1+B.N2+B.N3)
FROM B
WHERE B.B1 = A.A
GROUP BY A.A
)
UPDATE A
SET N=(
SELECT SUM(A.N+B.N1+B.N2+B.N3) AS N
FROM A LEFT JOIN
B ON A.A=B.B1
GROUP BY A.A
)
呵呵,多了(
UPDATE A
SET N=N+ (
SELECT SUM(B.N1+B.N2+B.N3)
FROM B
WHERE B.B1 = A.A
GROUP BY A.A
)
UPDATE (SELECT MAX(A.N) AN,
SUM(B.N1+B.N2+B.N3)+MAX(A.N) AS NN
FROM (SELECT 'A1' A,7 N FROM DUAL
UNION ALL
SELECT 'A2' A,8 N FROM DUAL
)A,
(
SELECT 'A1' B1,1 N1,2 N2,3 N3 FROM DUAL
UNION ALL
SELECT 'A1' B1,4 N1,5 N2,6 N3 FROM DUAL
UNION ALL
SELECT 'A2' B1,1 N1,2 N2,3 N3 FROM DUAL
)B
WHERE A.A = B.B1
GROUP BY A.A
)
SET AN = NN;
-- 2ND WAY:
UPDATE A
SET A.N = (SELECT SUM(B.N1+B.N2+B.N3)+MAX(A.N) AS NN
FROM B
WHERE A.A = B.B1
GROUP BY A.A
)
WHERE EXISTS (SELECT 1
FROM B
WHERE A.A = B.B1);
UNION ALL
SELECT 'A2' A,8 N FROM DUAL
)A,
(
SELECT 'A1' B1,1 N1,2 N2,3 N3 FROM DUAL
UNION ALL
SELECT 'A1' B1,4 N1,5 N2,6 N3 FROM DUAL
UNION ALL
SELECT 'A2' B1,1 N1,2 N2,3 N3 FROM DUAL
)B
上面是我凑成的结果集,你测试的时候可以改成 A,B