两个表,表a,表b表aID NAME COUNT1 COUNT2 SUM1 COUNT3 COUNT4 SUM2
1 a 11 22 33 5 7 12
2 b 2 7 9 1 3 4
3 c 4 7 11 2 3 5
...
表bID NAME COUNT1 COUNT2 SUM1
1 a 1 3 4
2 b 3 4 7
...结果集c
ID NAME COUNT1 COUNT2 SUM1 COUNT3 COUNT4 SUM2 SUM1_MIX
1 a 11 22 33 5 7 12 37
2 b 2 7 9 1 3 4 16
3 c 4 7 11 2 3 5 7
...若表a有1000行,表b有10行,现在要得到如下结果集(计算表a,表b相同name的sum1合计值SUM1_MIX,表a记录全部显示),这个sql语句该如何写呀?
1 a 11 22 33 5 7 12
2 b 2 7 9 1 3 4
3 c 4 7 11 2 3 5
...
表bID NAME COUNT1 COUNT2 SUM1
1 a 1 3 4
2 b 3 4 7
...结果集c
ID NAME COUNT1 COUNT2 SUM1 COUNT3 COUNT4 SUM2 SUM1_MIX
1 a 11 22 33 5 7 12 37
2 b 2 7 9 1 3 4 16
3 c 4 7 11 2 3 5 7
...若表a有1000行,表b有10行,现在要得到如下结果集(计算表a,表b相同name的sum1合计值SUM1_MIX,表a记录全部显示),这个sql语句该如何写呀?
FROM A, B
WHERE A.NAME = B.NAME
如果是的话就这样:
SELECT *
FROM A, (SELECT A.ID,A.NAME,A.COUNT1,A.COUNT2,A.SUM1,A.COUNT3,A.COUNT4,A.SUM2,(A.SUM1 + B.SUM1) SUM1_MAX
FROM A, B
WHERE A.NAME = B.NAME) C
WHERE A.NAME = C.NAME(+)
SELECT A.*, (A.SUM1 + NVL(B.SUM1, 0)) SUM1_MAX
FROM A, B
WHERE A.NAME = B.NAME OR B.NAME IS NULL
对,表a要全部显示(包括b没有的也要显示)上一条未来得及试,最后一条只显示了表a与表b相同部分内容,可我需要显示表a的所有数据
FROM A, B
WHERE A.NAME = B.NAME(+)上面的是oracle8i的。
如果是9i则这样:SELECT A.*, (A.SUM1 + NVL(B.SUM1, 0)) SUM1_MAX
FROM A left outer join B on A.NAME = B.NAME