create tmp as
select a,b,a*c,0 as a1 from tableA where a>10;update tmp q
set q.a1 = (select sum(w.a1) from tableB w where w.b = q.b);select * from tmp;drop table tmp;这个是我简化了的SQL语句,请问有更简便的方法吗?
想用视图的,但不知道怎么写SQL语句好!因为不想每次都create表!万一多人用的时候就出错了!
select a,b,a*c,0 as a1 from tableA where a>10;update tmp q
set q.a1 = (select sum(w.a1) from tableB w where w.b = q.b);select * from tmp;drop table tmp;这个是我简化了的SQL语句,请问有更简便的方法吗?
想用视图的,但不知道怎么写SQL语句好!因为不想每次都create表!万一多人用的时候就出错了!
使用ORACLE全局临时表,一次创建,永久使用。
select q.a,q.b,q.a*q.c as ac,0 as a1 from tableA q,tableB w where w.b=q.b and q.a>10
select q.a,q.b,q.a*q.c as ac,0 as a1 from tableA q,tableB w where w.b=q.b and q.a>10
select a,b,a*c,0 as a1 from tableA where a>10;update tmp q
set q.a1 = (select sum(w.a1) from tableB w where w.b = q.b);select * from tmp;drop table tmp;再过程中写动态sql
from tableA t1, tableB t2
where t1.b=t2.b group by t1.a, t1.b, t1.c;详情见下面的试验SQL> create table t1(a number(10), b number(10), c number(10)); Table created.SQL> create table t2(a1 number(10), b number(10));Table created.SQL> insert into t1 values(1, 10, 100);1 row created.SQL> insert into t1 values(2, 20, 200);1 row created.SQL> insert into t2 values(5, 10);1 row created.SQL> insert into t2 values(7, 10);1 row created.SQL> insert into t2 values(8, 20);1 row created.SQL> insert into t2 values(9,20);1 row created.SQL> insert into t2 values(1000, 30);1 row created.SQL> select * from t1; A B C
---------- ---------- ----------
1 10 100
2 20 200SQL> select * from t2; A1 B
---------- ----------
5 10
7 10
8 20
9 20
1000 30SQL> select t1.a, t1.b, t1.a*t1.c, sum(t2.a1) from t1, t2
2 where t1.b=t2.b group by t1.a, t1.b, t1.c; A B T1.A*T1.C SUM(T2.A1)
---------- ---------- ---------- ----------
2 20 400 17
1 10 100 12
不需要create tablehttp://topic.csdn.net/u/20100110/16/9a933145-44f4-41ec-a860-41717c6bb369.htmlhttp://www.oracle.com/technology/products/oracle9i/daily/oct10.html试试这样可以执行吗update (with xx as (select x1, x2 from kkk where sss > kkk) select * from xx where kkk > sjls) q
set q.a1 = (select sum(w.a1) from tableB w where w.b = q.b);
FROM tableA q, (SELECT b, SUM(a1) total FROM tableB GROUP BY b) w
WHERE q.a > 10 AND
w.b = q.b;
where a.b=c.b(+) and a.a>10
group by a,b,a*c