求个高效的执行语句（oracle）

[buller]

表格式如下（字段分别为ID（自增）,项目ID，数量，累计数量）：

ID    xmid     sl    ljsl
1     001       2     2
2     002       3     3
3     003       2     2
4     001       1     3
5     004        2     2
6     006        1     1
7     001        2     5 
8     002        -2    1
  

累计数量列是没有的，这里我方便查看写的，累计数量等于此xmid对应小于等于本条数据ID的sl之和
数据可能有十几万条，xmid有几千不等，有什么高效的方式更新这个ljsl？或者程序实现好点？

[碧水幽幽泉]

可以优化，以下SQL搞定：

select id,xmid,s1,sum(s1)over(partition by xmid order by id) as ljsl from table_name;

[panjun198242]

select id,xmid,s1,sum(s1)over(partition by xmid order by id) as ljsl from table_name; 这个SQL确实可以

[buller]

引用 1 楼 qq646748739 的回复:

可以优化，以下SQL搞定：

select id,xmid,s1,sum(s1)over(partition by xmid order by id) as ljsl from table_name;

厉害！ 查询可以，不过更新到这个表应该怎么写？

update  table_name set ljsl=sum(s1)over(partition by xmid order by id)  

这个有语法错误，对oracle的update语句不得要领

[碧水幽幽泉]

update语句也非常简单

update table_name a 
   set a.ljsl = (select sum(s1)over(partition by xmid order by id) as ljsl from table_name b where b.id = a.id)
 where exists(select null from table_name b where b.id = a.id);

[学海无涯-回头是岸]

MERGE INTO T  A
USING (WITH T1 AS(SELECT T.*,ROWID AS RID,sum(s1)over(partition by xmid order by id)  AS SL)
SELECT * FROM T1)
ON(A.ROWID=T1.RID)
WHEN MATCHED THEN UPDATE SET A.LJSL=B.SL

[碧水幽幽泉]

数据量如果很大的话，就使用merge into

merge into table_name a using (select id,sum(s1)over(partition by xmid order by id) as ljsl from table_name) b
   on b.id = a.id
 when matched then  
   set a.ljsl = b.ljsl;
   
commit;

[buller]

谢谢

[碧水幽幽泉]

引用 5 楼 baidu_36457652 的回复:

MERGE INTO T  A
USING (WITH T1 AS(SELECT T.*,ROWID AS RID,sum(s1)over(partition by xmid order by id)  AS SL)
SELECT * FROM T1)
ON(A.ROWID=T1.RID)
WHEN MATCHED THEN UPDATE SET A.LJSL=B.SL

你这语句会报错的。
1.缺少别名。
2.逻辑不对：两个表的rowid匹配不上，更新0条数据。

[碧水幽幽泉]

重要的事只说一遍！

[碧水幽幽泉]

没问题的话，此楼结贴！

[学海无涯-回头是岸]

引用 8 楼 碧水幽幽泉的回复:

[quote=引用 5 楼 baidu_36457652 的回复:]

MERGE INTO T  A
USING (WITH T1 AS(SELECT T.*,ROWID AS RID,sum(s1)over(partition by xmid order by id)  AS SL)
SELECT * FROM T1)
ON(A.ROWID=T1.RID)
WHEN MATCHED THEN UPDATE SET A.LJSL=B.SL

你这语句会报错的。
1.缺少别名。
2.逻辑不对：两个表的rowid匹配不上，更新0条数据。
[/quote] 嗯嗯 using 后面少了个 别名 但是逻辑应该没问题吧，rowID 不会变啊

[学海无涯-回头是岸]

MERGE INTO T A USING (WITH T1 AS(SELECT T.*,ROWID AS RID,sum(s1)over(partition by xmid order by id) AS SL) SELECT * FROM T1) B ON(A.ROWID=B.RID) WHEN MATCHED THEN UPDATE SET A.LJSL=B.SL