SQL server scheduling problems-CodePudding

Write a job number, a total of four, the first name capital first letters, DiErSanWei intercept id number 9, 10, the fourth if the top three is naturally produced same serial number, for example: 1 name is wang, born in July 1984, the name is wang born in October 1984, 2 3, surnamed li born in March 1985, generated code should be L851, W841, W842, written in SQL?

CodePudding user response:

 
The create table # A (name varchar (20), id varchar (20)) 
# insert into A values (' king of dog egg ', '320112198407210001') 
# insert into A values (' lee dog egg ', '320112198507210001') 
# insert into A values (' two dogs', '320112198410210001') 

The select xx. The top three + convert (varchar (5), ROW_NUMBER () over (partition by top three order by 1) (select)) job number, 
Xx. Xx. The name, id date 
The from (
Select 
Case the when unicode (SUBSTRING (a. name, 1, 1)) between 19968 and 19968 + 20901 
Then (
Select the top 1 p y 
The from 
(
Select the 'A' as p y, N 'Ao' as word 
Union all select 'B', N 'book' 
Union all select 'C', N 'wrong' 
Union all select 'D', N '鵽' 
Union all select 'E', N 'Er' 
Union all select 'F', N 'abalone' 
Union all select 'G', N '腂' 
Union all select 'H', N '夻' 
Union all select 'J', N '攈' 
Union all select 'K', N '穒' 
Union all select 'L', N '鱳' 
Union all select the 'M', N '旀' 
Union all select 'N', N '桛' 
Union all select 'O', N 'retting' 
Union all select 'P', N 'exposure' 
Union all select 'Q', N '囕' 
Union all select 'R', N '鶸' 
Union all select the 'S', N '蜶' 
Union all select the 'T', N 'Tuo' 
Union all select 'W', N ' ' '
Union all select 'X', N '鑂' 
Union all select 'Y', N 'charm' 
Union all select 'Z', N 'throw' 
) T 
Where word>=the SUBSTRING (a. name, 1, 1) collate Chinese_PRC_CS_AS_KS_WS 
The order by PY ASC 
) 
Else the SUBSTRING (a. name, 1, 1) 
End + SUBSTRING (a. id number, 9, 2) the top three, a. # * from a xx 
 a)

Copying a large bunch of the code of the initials of the check