The order by grouping problem-CodePudding

I think the last column in the first column 5 to become 48, every code won't be so can't write same die

CodePudding user response:

Is to add the mantissa 00 to tail of 99 to above

CodePudding user response:

Unknown specific needs,,, what is the result

CodePudding user response:

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

CodePudding user response:

 
The 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' the FROM DUAL 
UNION ALL 
SELECT '11010102', '48' FROM DUAL 
UNION ALL 
SELECT '12010105', '6' FROM DUAL 
UNION ALL 
SELECT '12010106', '90' the 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 
June 12010107 50 
*/
The MERGE INTO (SELECT PAT1. * FROM PAT1 
The WHERE (REGION_CODE, SO) IN 
(SELECT REGION_CODE, SO the FROM 
(SELECT PAT1. *, ROW_NUMBER () OVER (PARTITION BY LPAD REGION_CODE, (6) ORDER BY REGION_CODE DESC) AS an RN FROM PAT1) M1 
WHERE M1. RN=1) T1 
USING the 
(the SELECT PAT1. * FROM PAT1 
The WHERE (REGION_CODE, SO) IN 
(SELECT REGION_CODE, SO the FROM (the SELECT PAT1. *, ROW_NUMBER () OVER (PARTITION BY LPAD REGION_CODE, (6) ORDER BY REGION_CODE ASC) AS an RN FROM PAT1) M1 
WHERE M1. RN=1) T2 
ON (LPAD (T1) REGION_CODE, 6)=LPAD (T2) REGION_CODE, 6)) 
The WHEN MATCHED THEN UPDATE the 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 
*/

That way?

CodePudding user response:

If not clear, thank two above answer, first I say under
The select DCT. Region_code
(case
The when s0 is null then
0
The else
S0
End s0)
The from (select t.r egpostcode, count (*) as s0
The from t_cdp02_t02_zlfx_dstj t
Where 1=1
And t.t JLX='2'
And t.j d='4'
And t.d eathdate & gt;
='2017-10-01'And t.d eathdate & lt;
='2017-12-31'And t.y Lin ear & gt;
='2017'And (t.S HSTATE='2' or t.S HSTATE is null)
And (t.I S_WFS IS NULL OR t.I S_WFS='2')
And t.i s_del='0'
And substr (t.r egpostcode, 1, 6)='110101'
Group by t.r egpostcode) tt
Right join (select d. *
The from t_sys_dct001 d
Where substr (d.r egion_code, 1, 6)='110101'
The order by d.r egion_code DCT)
On tt. Regpostcode=DCT. Region_code
This is the original SQL query to get 1st floor picture

Now need to put the first line of the second column 5, added to the last line of the second column, 43, such as

Because each time the condition is different, can't write death is now 11010100 is likely to be 11010200110030 next time,,, summary is the value of every time the results of the query digits of 00 plus DaoWeiShu value of 99

CodePudding user response:

Need to decode region_code at the end of the two, 00 to 99, other remain the same, then aggregate sum line ah

CodePudding user response:

It is also simple, add to, and then region_code take back that counts

CodePudding user response:

refer to 6th floor acen_chen response:

need to decode region_code at the end of the two, is set to 99, 00 other remain the same, then aggregate sum line ah

Not make ah, like this

CodePudding user response:

The

reference 7 floor baidu_36457652 response:

that is simple, just added and then region_code take back the

Did not understand ah, with what

CodePudding user response:

If this is the ORACLE database can write
The update table set so + (select the so from the table where rownum=1) from the table
Where the rowid=(select Max (rowid) from by Tate) - the last row