order by 分组问题

[飞翔的荷兰猪〃]

我想把第一列的5加到最后一列变成48，每次code都不会一样所以不能写死

[飞翔的荷兰猪〃]

引用 6 楼 acen_chen 的回复:

只需decode region_code末两位，为00的设为99，其他保持原样，然后聚合sum就行了呀

不好使啊，成这样了

[飞翔的荷兰猪〃]

引用 7 楼 baidu_36457652 的回复:

那就还简单些，加上去，然后region＿code取后面那个就是了

没有看懂啊。。加个什么啊

[wubing1111]

如果是ORACLE的数据库可以这样写 update table set so+(select so from table where rownum =1) from table where rowid=(select max(rowid) from tale) --取得最后一行的条件

[飞翔的荷兰猪〃]

就是要把尾数00的加到尾数99的上边去

[acen_chen]

不明具体需求。。。要的是什么结果

[卖水果的net]

update t 
set so = so + (select so from t where substr(code,-2) = '00')
where substr(code, -2) = '99'

[学海无涯-回头是岸]

CREATE TABLE PAT1 (REGION_CODE VARCHAR2(200),SO VARCHAR2(200))
TRUNCATE TABLE PAT1
INSERT INTO PAT1
SELECT '11010100','5' FROM DUAL
UNION ALL
SELECT '11010101','74' FROM DUAL
UNION ALL
SELECT '11010102','48' FROM DUAL
UNION ALL
SELECT '12010105','6' FROM DUAL
UNION ALL
SELECT '12010106','90' FROM DUAL
UNION ALL
SELECT '12010107','50' FROM DUAL

SELECT * FROM PAT1
/*
   	REGION_CODE	SO
1	11010100	5
2	11010101	74
3	11010102	48
4	12010105	6
5	12010106	90
6	12010107	50
*/
MERGE INTO (SELECT PAT1.* FROM  PAT1
 WHERE (REGION_CODE,SO) IN 
 (SELECT REGION_CODE,SO FROM  
(SELECT PAT1.*,ROW_NUMBER()OVER(PARTITION BY LPAD(REGION_CODE,6) ORDER BY REGION_CODE DESC ) AS RN FROM PAT1)M1
 WHERE  M1.RN=1) )T1
 USING 
 ( SELECT PAT1.* FROM  PAT1
  WHERE (REGION_CODE,SO) IN 
 (SELECT REGION_CODE,SO FROM  (SELECT PAT1.*,ROW_NUMBER()OVER(PARTITION BY LPAD(REGION_CODE,6) ORDER BY REGION_CODE ASC ) AS RN FROM PAT1)M1
 WHERE  M1.RN=1) )T2
 ON (LPAD(T1. REGION_CODE,6)= LPAD(T2.REGION_CODE,6))
 WHEN MATCHED THEN UPDATE SET T1.SO=T2.SO+T1.SO


SELECT * FROM PAT1
/*
   	REGION_CODE	SO
1	11010100	5
2	11010101	74
3	11010102	53
4	12010105	6
5	12010106	90
6	12010107	56
*/

是这个样子吗？

[飞翔的荷兰猪〃]

好像没说清楚，先谢谢上边两位回答，我再说下
select dct.region_code,
(case
when s0 is null then
0
else
s0
end) s0
from (select t.regpostcode, count(*) as s0
from t_cdp02_t02_zlfx_dstj t
where 1 = 1
and t.tjlx = '2'
and t.jd = '4'
and t.deathdate >= '2017-10-01'
and t.deathdate <= '2017-12-31'
and t.year >= '2017'
and (t.SHSTATE = '2' or t.SHSTATE is null)
and (t.IS_WFS IS NULL OR t.IS_WFS = '2')
and t.is_del = '0'
and substr(t.regpostcode, 1, 6) = '110101'
group by t.regpostcode) tt
right join (select d.*
from t_sys_dct001 d
where substr(d.region_code, 1, 6) = '110101'
order by d.region_code) dct
on tt.regpostcode = dct.region_code
这是原有sql 查询得到1楼图片

现在需要把第一行第二列的5，加到最后一行第二列的43中，类似这样
因为每次条件都不同，不能写死现在是11010100下次有可能是11010200。11010300。。。总结就是每次查询的结果位数为00的值加到位数为99的值上

[acen_chen]

只需decode region_code末两位，为00的设为99，其他保持原样，然后聚合sum就行了呀

[学海无涯-回头是岸]

那就还简单些，加上去，然后region＿code取后面那个就是了