楼上的语法在8i或者以上版本可以,不过我喜欢传统的写法: select x.id, sum(y.data) from tbl x, tbl y where x.id >= y.id;
我想实际表中的数据不一定就是按ID排序的,所以 couchman(couchman)的应该有个order by的select t2.id, (select sum(t1.data) from tab1 t1 where t1.id <= t2.id) data from tab1 t2 order by t2.id而弱水兄的少了一个group by. select x.id, sum(y.data) from tbl x, tbl y where x.id >= y.id group by x.id昨晚都测试一下,但是偶是菜鸟,执行计划还看不懂^_^ 不知道哪个更好一些
to KingSunSha(弱水三千) 您的写法不对,子查询(subquery)虽然可以改成连接(join). 但这里有求和汇总函数。 按您的写法,应该有Group by 语句出现才对!
如过是816以上版本呢 可以这样: SQL> select * from t1; A B ---------- ---------- 1 1 2 2 3 3 5 10 6 6 8 8 9 9已选择7行。SQL> select sum(a) over(order by b),a,b from t1;SUM(A)OVER(ORDERBYB) A B -------------------- ---------- ---------- 1 1 1 3 2 2 6 3 3 12 6 6 20 8 8 29 9 9 34 5 10已选择7行。SQL>
这样是不是更清楚啊. select id,data,sum(id) over(order by id rows unbounded preceding) as result from table.
关键是那种写法的效能最高。等我有空实验了再说。大家也可以试试。为了适合任何表,我是这样写的。 SELECT SUM(B.DATA) FROM (SELECT ROWNUM,TABLE.* FROM TABLE) A, (SELECT ROWNUM, TABLE.* FROM TABLE) B WHERE A.ROWNUM >= B.ROWNUM GROUP BY A.ROWNUM; 不过我觉得好象效率不太好。 上面有几位兄台的SQL小弟不太懂。如 select id,data,sum(id) over(order by id rows unbounded preceding) as result from table.还有select sum(a) over(order by b),a,b from t1;
select x.id, sum(y.data)
from tbl x, tbl y
where x.id >= y.id;
couchman(couchman)的应该有个order by的select t2.id,
(select sum(t1.data) from tab1 t1 where t1.id <= t2.id) data
from tab1 t2
order by t2.id而弱水兄的少了一个group by.
select x.id, sum(y.data)
from tbl x, tbl y
where x.id >= y.id
group by x.id昨晚都测试一下,但是偶是菜鸟,执行计划还看不懂^_^
不知道哪个更好一些
但这里有求和汇总函数。
按您的写法,应该有Group by 语句出现才对!
可以这样:
SQL> select * from t1; A B
---------- ----------
1 1
2 2
3 3
5 10
6 6
8 8
9 9已选择7行。SQL> select sum(a) over(order by b),a,b from t1;SUM(A)OVER(ORDERBYB) A B
-------------------- ---------- ----------
1 1 1
3 2 2
6 3 3
12 6 6
20 8 8
29 9 9
34 5 10已选择7行。SQL>
select id,data,sum(id) over(order by id rows unbounded preceding) as result from table.
SELECT SUM(B.DATA) FROM
(SELECT ROWNUM,TABLE.* FROM TABLE) A,
(SELECT ROWNUM, TABLE.* FROM TABLE) B
WHERE A.ROWNUM >= B.ROWNUM GROUP BY A.ROWNUM;
不过我觉得好象效率不太好。
上面有几位兄台的SQL小弟不太懂。如
select id,data,sum(id) over(order by id rows unbounded preceding) as result from table.还有select sum(a) over(order by b),a,b from t1;
2:oracle document