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 DUALSELECT * 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的值上
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 DUALSELECT * 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的值上
update table set so+(select so from table where rownum =1) from table
where rowid=(select max(rowid) from tale) --取得最后一行的条件