CustomerCode sort in the table is disordered, we have to deal with this batch of data,
1. Write a SQL statement to take CustomerCode column after a maximum of five digital structure



2. Generate new way of coding inserted into a NewCode, encoding requirements in the form of C00001,
Where C is fixed, according to the CustomerCode behind five after five ascending, starting from 1

CodePudding user response:

Select t. *, 'C' | | lpad (row_number () over (order by to_number (substr (t.a aa, length (t.a aa) - (4))), '5', '0') cn
The from t_aa t;
Refer to this, change yourself,

CodePudding user response:

The update t_aa t
The set t.a _path=
(select cn
The from (select 'C' | |
Lpad (row_number ()
Over (order by
To_number (substr (a.a aa, length (a.a aa) - (4))),
'5',
'0') as cn,
Anderson, d
The from TMP t_aa a)
Where t.i d=TMP. Id);
I built the test table, this statement line
To change the corresponding field name and the name of the table,

CodePudding user response:

Needs to be updated according to the id or generated newcode directly according to the order from big to small in the can?

CodePudding user response:

First SELECT MAX (TO_NUMBER (SUBSTR (customercode, 5))) as maxnum from t

CodePudding user response:

reference baidu_36457652 reply: 3/f

needs to be updated according to the id or generated newcode directly according to the order from big to small in the can?

The first ask newly generated code, inserted into the NewCode this column, encoding requirements, by means of C00001
Where C is fixed, according to the CustomerCode behind five after five ascending, starting from 1, according to the ascending order

CodePudding user response:

reference 5 floor Sprit_lc reply:

Quote: refer to the third floor baidu_36457652 response:

Needs to be updated according to the id or generated newcode directly according to the order from big to small in the can?

The first ask newly generated code, inserted into the NewCode this column, encoding requirements, by means of C00001
Where C is fixed, according to the CustomerCode behind five after five ascending, starting from 1, according to the ascending order

I mean, for example the newcode id equal to 2 should be C10003, if id=100 C00002, are you going to the back of the result is id=2 is C10003 C00002 or sorted the second value

CodePudding user response:

reference 5 floor Sprit_lc reply:

Quote: refer to the third floor baidu_36457652 response:

Needs to be updated according to the id or generated newcode directly according to the order from big to small in the can?

The first ask newly generated code, inserted into the NewCode this column, encoding requirements, by means of C00001
Where C is fixed, according to the CustomerCode behind five after five ascending, starting from 1, according to the ascending order

I don't give the SQL can meet your requirements, you see, you will know