撰寫一個工號，一共4位，第一位姓名大寫首字母，第二三位截取身份證號9,10位，第四位如果前三位相同就自然產生序號，例如：1姓王，1984年7月生人，2姓王 1984年10月生人，3、姓李 1985年3月生人，生成編碼就應該是L851,W841,W842。用sql 怎么撰寫？
uj5u.com熱心網友回復：
create table #A (姓名 varchar(20),身份證號 varchar(20))
insert into #A values ('王狗蛋','320112198407210001')
insert into #A values ('李狗蛋','320112198507210001')
insert into #A values ('王二狗','320112198410210001')
select xx.前三位 + convert(varchar(5),ROW_NUMBER() over(partition by 前三位 order by (select 1))) 工號,
xx.姓名,xx.身份證號
from (
select
case when unicode(SUBSTRING(a.姓名,1,1)) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
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 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=SUBSTRING(a.姓名,1,1) collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else SUBSTRING(a.姓名,1,1)
end + SUBSTRING(a.身份證號,9,2) 前三位,a.* from #A a) xx
復制了一大串查首字母的代碼
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/233292.html
標籤：應用實例