begin
CREATE TABLE t1
(a NUMBER,
b NUMBER)
/
CREATE TABLE t2
(a NUMBER,
c NUMBER)
/
insert into t1(a,b)
values(1,10);
insert into t1(a,b)
values(1,11);
insert into t1(a,b)
values(2,20);
insert into t1(a,b)
values(2,22);
/
insert into t2(a,c)
values(1,4);
insert into t2(a,c)
values(1,5);
insert into t2(a,c)
values(2,5);
insert into t2(a,c)
values(2,6);
insert into t2(a,c)
values(2,7);
/
select a, sum(b) - (select sum(c) from t2 where a = t1.a) as b from t1 group by a
/*结果:
A B
1 12
2 24
*/
CREATE TABLE t1
(a NUMBER,
b NUMBER)
/
CREATE TABLE t2
(a NUMBER,
c NUMBER)
/
insert into t1(a,b)
values(1,10);
insert into t1(a,b)
values(1,11);
insert into t1(a,b)
values(2,20);
insert into t1(a,b)
values(2,22);
/
insert into t2(a,c)
values(1,4);
insert into t2(a,c)
values(1,5);
insert into t2(a,c)
values(2,5);
insert into t2(a,c)
values(2,6);
insert into t2(a,c)
values(2,7);
/
select a, sum(b) - (select sum(c) from t2 where a = t1.a) as b from t1 group by a
/*结果:
A B
1 12
2 24
*/
解决方案 »
- 关于合并部分相同字段的值的sql,进来看看 谢谢。
- 关于ORACLE不支持某些罕见汉字的问题.求帮助...
- SELECT NAME FROM MOBILE_MAIN NATURAL JOIN MOBILE_SUB关于natural join
- 如何对结果集进行统计
- Oracle sql语句 求帮帮忙~~
- 问个简单问题-sql server转oracle的问题(只有十分了)
- 冰天雪地鲜血淋漓后空翻360度跪求一个数据备份命令问题
- 谁有oracle 的数据库恢复工具dul?
- 出现错误:Ora:01041内部错误,HostDef扩展名不存在!什么原因,如何解决??
- 关于Oracle 9i数据库的备份!
- 一个统计查询的问题
- 那个更快
select a, sum(b) - (select sum(c) from t2 where a = t1.a) as b from t1 group by a
*
ERROR 位于第 1 行:
ORA-00979: 不是 GROUP BY 表达式
但是我要在8.16下运行,oracle不会这么傻吧
子查询作为表(from list) oracle称为 inline-view
8I的过程不支持,但过程外支持;9I可以
(select a, sum( b ) as b from t1 group by a) t1,
(select a, sum( c ) as c from t2 group by a) t2 where t1.a = t2.a;这样可以了
oracle太弱智了,到了9i才改善。