去年1月份時寫了一篇關于優化漢字轉拼音函數的解決方案,當時重點在于優化所舉案例隻能解決漢字轉為拼音的首字母。
前不久收到這樣一條短消息
随之我從(CSDN,CNBLOG,51CTO等)搜了幾個解決方案,性能确實都不大理想。
首先還是感謝園友這個問題,接下來我就從網上抽取兩個案例作優化說明
函數要求實作功能
select 函數名 ('你好,我是追索')
傳回的結果(ni hao , wo shi zhui suo)
解決方案一：
解決方案一
/*
根據漢字擷取全拼
1.生成所有讀音臨時表
2.根據Chinese_PRC_CS_AS_KS_WS 排序擷取讀音
*/
create function dbo.fn_Getquanpin1(@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000)
--生成臨時表
declare @t table(chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))
insert into @t select'吖','a'
insert into @t select'厑','aes'
insert into @t select'哎','ai'
insert into @t select'安','an'
insert into @t select'肮','ang'
insert into @t select'凹','ao'
insert into @t select'八','ba'
insert into @t select'挀','bai'
insert into @t select'兡','baike'
insert into @t select'瓸','baiwa'
insert into @t select'扳','ban'
insert into @t select'邦','bang'
insert into @t select'勹','bao'
insert into @t select'萡','be'
insert into @t select'陂','bei'
insert into @t select'奔','ben'
insert into @t select'伻','beng'
insert into @t select'皀','bi'
insert into @t select'邊','bian'
insert into @t select'辪','uu'
insert into @t select'灬','biao'
insert into @t select'憋','bie'
insert into @t select'汃','bin'
insert into @t select'冫','bing'
insert into @t select'癶','bo'
insert into @t select'峬','bu'
insert into @t select'嚓','ca'
insert into @t select'偲','cai'
insert into @t select'乲','cal'
insert into @t select'參','can'
insert into @t select'倉','cang'
insert into @t select'撡','cao'
insert into @t select'冊','ce'
insert into @t select'膥','cen'
insert into @t select'噌','ceng'
insert into @t select'硛','ceok'
insert into @t select'岾','ceom'
insert into @t select'猠','ceon'
insert into @t select'乽','ceor'
insert into @t select'叉','cha'
insert into @t select'犲','chai'
insert into @t select'辿','chan'
insert into @t select'伥','chang'
insert into @t select'抄','chao'
insert into @t select'車','che'
insert into @t select'抻','chen'
insert into @t select'阷','cheng'
insert into @t select'吃','chi'
insert into @t select'充','chong'
insert into @t select'抽','chou'
insert into @t select'出','chu'
insert into @t select'膗','chuai'
insert into @t select'巛','chuan'
insert into @t select'刅','chuang'
insert into @t select'吹','chui'
insert into @t select'旾','chun'
insert into @t select'踔','chuo'
insert into @t select'呲','ci'
insert into @t select'嗭','cis'
insert into @t select'從','cong'
insert into @t select'湊','cou'
insert into @t select'粗','cu'
insert into @t select'汆','cuan'
insert into @t select'崔','cui'
insert into @t select'邨','cun'
insert into @t select'瑳','cuo'
insert into @t select'撮','chua'
insert into @t select'咑','da'
insert into @t select'呔','dai'
insert into @t select'丹','dan'
insert into @t select'當','dang'
insert into @t select'刀','dao'
insert into @t select'恴','de'
insert into @t select'揼','dem'
insert into @t select'扥','den'
insert into @t select'燈','deng'
insert into @t select'仾','di'
insert into @t select'嗲','dia'
insert into @t select'敁','dian'
insert into @t select'刁','diao'
insert into @t select'爹','die'
insert into @t select'哋','dei'
insert into @t select'嚸','dim'
insert into @t select'丁','ding'
insert into @t select'丟','diu'
insert into @t select'東','dong'
insert into @t select'吺','dou'
insert into @t select'剢','du'
insert into @t select'耑','duan'
insert into @t select'叾','dug'
insert into @t select'垖','dui'
insert into @t select'噸','dun'
insert into @t select'咄','duo'
insert into @t select'妸','e'
insert into @t select'奀','en'
insert into @t select'鞥','eng'
insert into @t select'仒','eo'
insert into @t select'乻','eol'
insert into @t select'旕','eos'
insert into @t select'兒','er'
insert into @t select'發','fa'
insert into @t select'帆','fan'
insert into @t select'匚','fang'
insert into @t select'飛','fei'
insert into @t select'吩','fen'
insert into @t select'豐','feng'
insert into @t select'瓰','fenwa'
insert into @t select'覅','fiao'
insert into @t select'仏','fo'
insert into @t select'垺','fou'
insert into @t select'夫','fu'
insert into @t select'猤','fui'
insert into @t select'旮','ga'
insert into @t select'侅','gai'
insert into @t select'甘','gan'
insert into @t select'岡','gang'
insert into @t select'臯','gao'
insert into @t select'戈','ge'
insert into @t select'給','gei'
insert into @t select'根','gen'
insert into @t select'更','geng'
insert into @t select'啹','geu'
insert into @t select'喼','gib'
insert into @t select'嗰','go'
insert into @t select'工','gong'
insert into @t select'兝','gongfen'
insert into @t select'兣','gongli'
insert into @t select'勾','gou'
insert into @t select'估','gu'
insert into @t select'瓜','gua'
insert into @t select'乖','guai'
insert into @t select'關','guan'
insert into @t select'光','guang'
insert into @t select'歸','gui'
insert into @t select'丨','gun'
insert into @t select'呙','guo'
insert into @t select'妎','ha'
insert into @t select'咍','hai'
insert into @t select'乤','hal'
insert into @t select'兯','han'
insert into @t select'魧','hang'
insert into @t select'茠','hao'
insert into @t select'兞','haoke'
insert into @t select'诃','he'
insert into @t select'黒','hei'
insert into @t select'拫','hen'
insert into @t select'亨','heng'
insert into @t select'囍','heui'
insert into @t select'乊','ho'
insert into @t select'乥','hol'
insert into @t select'叿','hong'
insert into @t select'齁','hou'
insert into @t select'乎','hu'
insert into @t select'花','hua'
insert into @t select'徊','huai'
insert into @t select'歡','huan'
insert into @t select'巟','huang'
insert into @t select'灰','hui'
insert into @t select'昏','hun'
insert into @t select'吙','huo'
insert into @t select'嚿','geo'
insert into @t select'夻','hwa'
insert into @t select'丌','ji'
insert into @t select'加','jia'
insert into @t select'嗧','jialun'
insert into @t select'戋','jian'
insert into @t select'江','jiang'
insert into @t select'艽','jiao'
insert into @t select'階','jie'
insert into @t select'巾','jin'
insert into @t select'坕','jing'
insert into @t select'冂','jiong'
insert into @t select'丩','jiu'
insert into @t select'欍','jou'
insert into @t select'凥','ju'
insert into @t select'姢','juan'
insert into @t select'噘','jue'
insert into @t select'軍','jun'
insert into @t select'咔','ka'
insert into @t select'開','kai'
insert into @t select'乫','kal'
insert into @t select'刊','kan'
insert into @t select'冚','hem'
insert into @t select'砊','kang'
insert into @t select'尻','kao'
insert into @t select'坷','ke'
insert into @t select'肎','ken'
insert into @t select'劥','keng'
insert into @t select'巪','keo'
insert into @t select'乬','keol'
insert into @t select'唟','keos'
insert into @t select'厼','keum'
insert into @t select'怾','ki'
insert into @t select'空','kong'
insert into @t select'廤','kos'
insert into @t select'摳','kou'
insert into @t select'扝','ku'
insert into @t select'誇','kua'
insert into @t select'蒯','kuai'
insert into @t select'寬','kuan'
insert into @t select'匡','kuang'
insert into @t select'虧','kui'
insert into @t select'坤','kun'
insert into @t select'拡','kuo'
insert into @t select'穒','kweok'
insert into @t select'垃','la'
insert into @t select'來','lai'
insert into @t select'蘭','lan'
insert into @t select'啷','lang'
insert into @t select'撈','lao'
insert into @t select'仂','le'
insert into @t select'雷','lei'
insert into @t select'塄','leng'
insert into @t select'唎','li'
insert into @t select'倆','lia'
insert into @t select'嫾','lian'
insert into @t select'簗','liang'
insert into @t select'蹽','liao'
insert into @t select'毟','lie'
insert into @t select'厸','lin'
insert into @t select'伶','ling'
insert into @t select'溜','liu'
insert into @t select'瓼','liwa'
insert into @t select'囖','lo'
insert into @t select'龍','long'
insert into @t select'婁','lou'
insert into @t select'噜','lu'
insert into @t select'驢','lv'
insert into @t select'寽','lue'
insert into @t select'孿','luan'
insert into @t select'掄','lun'
insert into @t select'頱','luo'
insert into @t select'呣','m'
insert into @t select'媽','ma'
insert into @t select'遤','hweong'
insert into @t select'埋','mai'
insert into @t select'颟','man'
insert into @t select'牤','mang'
insert into @t select'匁','mangmi'
insert into @t select'貓','mao'
insert into @t select'唜','mas'
insert into @t select'庅','me'
insert into @t select'呅','mei'
insert into @t select'椚','men'
insert into @t select'掹','meng'
insert into @t select'踎','meo'
insert into @t select'瞇','mi'
insert into @t select'宀','mian'
insert into @t select'喵','miao'
insert into @t select'乜','mie'
insert into @t select'瓱','miliklanm'
insert into @t select'民','min'
insert into @t select'冧','lem'
insert into @t select'名','ming'
insert into @t select'謬','miu'
insert into @t select'摸','mo'
insert into @t select'乮','mol'
insert into @t select'哞','mou'
insert into @t select'母','mu'
insert into @t select'旀','myeo'
insert into @t select'丆','myeon'
insert into @t select'椧','myeong'
insert into @t select'拏','na'
insert into @t select'腉','nai'
insert into @t select'囡','nan'
insert into @t select'囔','nang'
insert into @t select'乪','keg'
insert into @t select'孬','nao'
insert into @t select'疒','ne'
insert into @t select'娞','nei'
insert into @t select'焾','nem'
insert into @t select'嫩','nen'
insert into @t select'莻','neus'
insert into @t select'鈪','ngag'
insert into @t select'銰','ngai'
insert into @t select'啱','ngam'
insert into @t select'妮','ni'
insert into @t select'年','nian'
insert into @t select'娘','niang'
insert into @t select'茑','niao'
insert into @t select'捏','nie'
insert into @t select'脌','nin'
insert into @t select'甯','ning'
insert into @t select'牛','niu'
insert into @t select'農','nong'
insert into @t select'羺','nou'
insert into @t select'奴','nu'
insert into @t select'女','nv'
insert into @t select'瘧','nue'
insert into @t select'瘧','nve'
insert into @t select'奻','nuan'
insert into @t select'黁','nun'
insert into @t select'燶','nung'
insert into @t select'挪','nuo'
insert into @t select'筽','o'
insert into @t select'夞','oes'
insert into @t select'乯','ol'
insert into @t select'鞰','on'
insert into @t select'讴','ou'
insert into @t select'妑','pa'
insert into @t select'俳','pai'
insert into @t select'磗','pak'
insert into @t select'眅','pan'
insert into @t select'乓','pang'
insert into @t select'抛','pao'
insert into @t select'呸','pei'
insert into @t select'瓫','pen'
insert into @t select'匉','peng'
insert into @t select'浌','peol'
insert into @t select'巼','phas'
insert into @t select'闏','phdeng'
insert into @t select'乶','phoi'
insert into @t select'喸','phos'
insert into @t select'丕','pi'
insert into @t select'囨','pian'
insert into @t select'缥','piao'
insert into @t select'氕','pie'
insert into @t select'丿','pianpang'
insert into @t select'姘','pin'
insert into @t select'乒','ping'
insert into @t select'钋','po'
insert into @t select'剖','pou'
insert into @t select'哣','deo'
insert into @t select'兺','ppun'
insert into @t select'仆','pu'
insert into @t select'七','qi'
insert into @t select'掐','qia'
insert into @t select'千','qian'
insert into @t select'羌','qiang'
insert into @t select'兛','qianke'
insert into @t select'瓩','qianwa'
insert into @t select'悄','qiao'
insert into @t select'苆','qie'
insert into @t select'親','qin'
insert into @t select'蠄','kem'
insert into @t select'氫','qing'
insert into @t select'銎','qiong'
insert into @t select'丘','qiu'
insert into @t select'曲','qu'
insert into @t select'迲','keop'
insert into @t select'峑','quan'
insert into @t select'蒛','que'
insert into @t select'夋','qun'
insert into @t select'亽','ra'
insert into @t select'囕','ram'
insert into @t select'呥','ran'
insert into @t select'穣','rang'
insert into @t select'荛','rao'
insert into @t select'惹','re'
insert into @t select'人','ren'
insert into @t select'扔','reng'
insert into @t select'日','ri'
insert into @t select'栄','rong'
insert into @t select'禸','rou'
insert into @t select'嶿','ru'
insert into @t select'撋','ruan'
insert into @t select'桵','rui'
insert into @t select'閏','run'
insert into @t select'叒','ruo'
insert into @t select'仨','sa'
insert into @t select'栍','saeng'
insert into @t select'毢','sai'
insert into @t select'虄','sal'
insert into @t select'三','san'
insert into @t select'桒','sang'
insert into @t select'掻','sao'
insert into @t select'色','se'
insert into @t select'裇','sed'
insert into @t select'聓','sei'
insert into @t select'森','sen'
insert into @t select'鬙','seng'
insert into @t select'閪','seo'
insert into @t select'縇','seon'
insert into @t select'殺','sha'
insert into @t select'篩','shai'
insert into @t select'山','shan'
insert into @t select'傷','shang'
insert into @t select'弰','shao'
insert into @t select'奢','she'
insert into @t select'申','shen'
insert into @t select'升','sheng'
insert into @t select'屍','shi'
insert into @t select'兙','shike'
insert into @t select'瓧','shiwa'
insert into @t select'収','shou'
insert into @t select'書','shu'
insert into @t select'刷','shua'
insert into @t select'摔','shuai'
insert into @t select'闩','shuan'
insert into @t select'雙','shuang'
insert into @t select'誰','shei'
insert into @t select'脽','shui'
insert into @t select'吮','shun'
insert into @t select'哾','shuo'
insert into @t select'絲','si'
insert into @t select'螦','so'
insert into @t select'乺','sol'
insert into @t select'忪','song'
insert into @t select'凁','sou'
insert into @t select'蘇','su'
insert into @t select'痠','suan'
insert into @t select'夊','sui'
insert into @t select'孫','sun'
insert into @t select'娑','suo'
insert into @t select'他','ta'
insert into @t select'襨','tae'
insert into @t select'囼','tai'
insert into @t select'坍','tan'
insert into @t select'铴','tang'
insert into @t select'仐','tao'
insert into @t select'畓','tap'
insert into @t select'忒','te'
insert into @t select'膯','teng'
insert into @t select'唞','teo'
insert into @t select'朰','teul'
insert into @t select'剔','ti'
insert into @t select'天','tian'
insert into @t select'旫','tiao'
insert into @t select'怗','tie'
insert into @t select'廳','ting'
insert into @t select'乭','tol'
insert into @t select'囲','tong'
insert into @t select'偷','tou'
insert into @t select'凸','tu'
insert into @t select'湍','tuan'
insert into @t select'推','tui'
insert into @t select'旽','tun'
insert into @t select'乇','tuo'
insert into @t select'屲','wa'
insert into @t select'歪','wai'
insert into @t select'乛','wan'
insert into @t select'尣','wang'
insert into @t select'危','wei'
insert into @t select'塭','wen'
insert into @t select'翁','weng'
insert into @t select'撾','wo'
insert into @t select'烏','wu'
insert into @t select'夕','xi'
insert into @t select'诶','ei'
insert into @t select'疨','xia'
insert into @t select'仙','xian'
insert into @t select'鄉','xiang'
insert into @t select'灱','xiao'
insert into @t select'楔','xie'
insert into @t select'心','xin'
insert into @t select'星','xing'
insert into @t select'兇','xiong'
insert into @t select'休','xiu'
insert into @t select'旴','xu'
insert into @t select'昍','xuan'
insert into @t select'疶','xue'
insert into @t select'坃','xun'
insert into @t select'丫','ya'
insert into @t select'咽','yan'
insert into @t select'欕','eom'
insert into @t select'央','yang'
insert into @t select'吆','yao'
insert into @t select'椰','ye'
insert into @t select'膶','yen'
insert into @t select'一','yi'
insert into @t select'乁','i'
insert into @t select'乚','yin'
insert into @t select'應','ying'
insert into @t select'喲','yo'
insert into @t select'傭','yong'
insert into @t select'優','you'
insert into @t select'迂','yu'
insert into @t select'囦','yuan'
insert into @t select'曰','yue'
insert into @t select'蒀','yun'
insert into @t select'帀','za'
insert into @t select'災','zai'
insert into @t select'兂','zan'
insert into @t select'牂','zang'
insert into @t select'遭','zao'
insert into @t select'啫','ze'
insert into @t select'賊','zei'
insert into @t select'怎','zen'
insert into @t select'曽','zeng'
insert into @t select'吒','zha'
insert into @t select'甴','gad'
insert into @t select'夈','zhai'
insert into @t select'氈','zhan'
insert into @t select'張','zhang'
insert into @t select'钊','zhao'
insert into @t select'蜇','zhe'
insert into @t select'貞','zhen'
insert into @t select'凧','zheng'
insert into @t select'之','zhi'
insert into @t select'中','zhong'
insert into @t select'州','zhou'
insert into @t select'劯','zhu'
insert into @t select'抓','zhua'
insert into @t select'專','zhuan'
insert into @t select'轉','zhuai'
insert into @t select'妝','zhuang'
insert into @t select'骓','zhui'
insert into @t select'宒','zhun'
insert into @t select'卓','zhuo'
insert into @t select'孜','zi'
insert into @t select'唨','zo'
insert into @t select'宗','zong'
insert into @t select'棸','zou'
insert into @t select'哫','zu'
insert into @t select'劗','zuan'
insert into @t select'厜','zui'
insert into @t select'尊','zun'
insert into @t select'昨','zuo'
declare @strlen int
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=py+' '[email protected],@[email protected]
from @t a where chr<=substring(@str,@strlen,1)
order by chr collate Chinese_PRC_CS_AS_KS_WS desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)[email protected],@[email protected]
end
return(@re)
end 測試了一串字元這個案例确實能達到漢字轉拼音要求,但當我看到此函數内declare table、insert、order by等等這些耗性能的關鍵詞讓我感到頭疼
我們還是來看看它的執行開銷
估計子樹大小大于5,這是多麼驚人的一個數字。此執行計劃後面跟着茫茫多的線條幾乎全是表插入
我用一萬條資料測試該函數等了我1分多鐘都沒執行完畢,可見該解決方案性能差到極點。
接下來我們看看解決方案二
解決方案二
create function [dbo].[fn_Getquanpin2](@words nvarchar(2000))
returns varchar(8000)
as
begin
declare @word nchar(1)
declare @pinyin varchar(8000)
declare @i int
declare @words_len int
declare @unicode int
set @i = 1
set @words = ltrim(rtrim(@words))
set @words_len = len(@words)
while (@i <= @words_len) --循環取字元
begin
set @word = substring(@words, @i, 1)
set @unicode = unicode(@word)
set @pinyin = ISNULL(@pinyin +SPACE(1),'')+
(case when unicode(@word) between 19968 and 19968+20901 then
(select top 1 py from (
select 'a' as py,N'厑' as word
union all select 'ai',N'靉'
union all select 'an',N'黯'
union all select 'ang',N'醠'
union all select 'ao',N'驁'
union all select 'ba',N'欛'
union all select 'bai',N'瓸' --韛兡瓸
union all select 'ban',N'瓣'
union all select 'bang',N'鎊'
union all select 'bao',N'鑤'
union all select 'bei',N'鐾'
union all select 'ben',N'輽'
union all select 'beng',N'鏰'
union all select 'bi',N'鼊'
union all select 'bian',N'變'
union all select 'biao',N'鰾'
union all select 'bie',N'彆'
union all select 'bin',N'鬢'
union all select 'bing',N'靐'
union all select 'bo',N'蔔'
union all select 'bu',N'簿'
union all select 'ca',N'囃'
union all select 'cai',N'乲' --縩乲
union all select 'can',N'爘'
union all select 'cang',N'賶'
union all select 'cao',N'鼜'
union all select 'ce',N'簎'
union all select 'cen',N'笒'
union all select 'ceng',N'乽' --硛硳岾猠乽
union all select 'cha',N'詫'
union all select 'chai',N'囆'
union all select 'chan',N'顫'
union all select 'chang',N'韔'
union all select 'chao',N'觘'
union all select 'che',N'爡'
union all select 'chen',N'讖'
union all select 'cheng',N'秤'
union all select 'chi',N'鷘'
union all select 'chong',N'銃'
union all select 'chou',N'殠'
union all select 'chu',N'矗'
union all select 'chuai',N'踹'
union all select 'chuan',N'鶨'
union all select 'chuang',N'愴'
union all select 'chui',N'顀'
union all select 'chun',N'蠢'
union all select 'chuo',N'縒'
union all select 'ci',N'嗭' --賜嗭
union all select 'cong',N'謥'
union all select 'cou',N'輳'
union all select 'cu',N'顣'
union all select 'cuan',N'爨'
union all select 'cui',N'臎'
union all select 'cun',N'籿'
union all select 'cuo',N'錯'
union all select 'da',N'橽'
union all select 'dai',N'靆'
union all select 'dan',N'饏'
union all select 'dang',N'闣'
union all select 'dao',N'纛'
union all select 'de',N'的'
union all select 'den',N'扽'
union all select 'deng',N'鐙'
union all select 'di',N'螮'
union all select 'dia',N'嗲'
union all select 'dian',N'驔'
union all select 'diao',N'鑃'
union all select 'die',N'嚸' --眰嚸
union all select 'ding',N'顁'
union all select 'diu',N'銩'
union all select 'dong',N'霘'
union all select 'dou',N'鬭'
union all select 'du',N'蠹'
union all select 'duan',N'叾' --籪叾
union all select 'dui',N'譵'
union all select 'dun',N'踲'
union all select 'duo',N'鵽'
union all select 'e',N'鱷'
union all select 'en',N'摁'
union all select 'eng',N'鞥'
union all select 'er',N'樲'
union all select 'fa',N'髮'
union all select 'fan',N'瀪'
union all select 'fang',N'放'
union all select 'fei',N'靅'
union all select 'fen',N'鱝'
union all select 'feng',N'覅'
union all select 'fo',N'梻'
union all select 'fou',N'鴀'
union all select 'fu',N'猤' --鰒猤
union all select 'ga',N'魀'
union all select 'gai',N'瓂'
union all select 'gan',N'灨'
union all select 'gang',N'戇'
union all select 'gao',N'鋯'
union all select 'ge',N'獦'
union all select 'gei',N'給'
union all select 'gen',N'搄'
union all select 'geng',N'堩' --亙堩啹喼嗰
union all select 'gong',N'兣' --熕贑兝兣
union all select 'gou',N'購'
union all select 'gu',N'顧'
union all select 'gua',N'詿'
union all select 'guai',N'恠'
union all select 'guan',N'鱹'
union all select 'guang',N'撗'
union all select 'gui',N'鱥'
union all select 'gun',N'謴'
union all select 'guo',N'腂'
union all select 'ha',N'哈'
union all select 'hai',N'饚'
union all select 'han',N'鶾'
union all select 'hang',N'沆'
union all select 'hao',N'兞'
union all select 'he',N'靏'
union all select 'hei',N'嬒'
union all select 'hen',N'恨'
union all select 'heng',N'堼' --堼囍
union all select 'hong',N'鬨'
union all select 'hou',N'鱟'
union all select 'hu',N'鸌'
union all select 'hua',N'蘳'
union all select 'huai',N'蘾'
union all select 'huan',N'鰀'
union all select 'huang',N'鎤'
union all select 'hui',N'顪'
union all select 'hun',N'諢'
union all select 'huo',N'夻'
union all select 'ji',N'驥'
union all select 'jia',N'嗧'
union all select 'jian',N'鑳'
union all select 'jiang',N'謽'
union all select 'jiao',N'釂'
union all select 'jie',N'繲'
union all select 'jin',N'齽'
union all select 'jing',N'竸'
union all select 'jiong',N'蘔'
union all select 'jiu',N'欍'
union all select 'ju',N'爠'
union all select 'juan',N'羂'
union all select 'jue',N'钁'
union all select 'jun',N'攈'
union all select 'ka',N'鉲'
union all select 'kai',N'乫' --鎎乫
union all select 'kan',N'矙'
union all select 'kang',N'閌'
union all select 'kao',N'鯌'
union all select 'ke',N'騍'
union all select 'ken',N'褃'
union all select 'keng',N'鏗' --巪乬唟厼怾
union all select 'kong',N'廤'
union all select 'kou',N'鷇'
union all select 'ku',N'嚳'
union all select 'kua',N'骻'
union all select 'kuai',N'鱠'
union all select 'kuan',N'窾'
union all select 'kuang',N'鑛'
union all select 'kui',N'鑎'
union all select 'kun',N'睏'
union all select 'kuo',N'穒'
union all select 'la',N'鞡'
union all select 'lai',N'籟'
union all select 'lan',N'糷'
union all select 'lang',N'唥'
union all select 'lao',N'軂'
union all select 'le',N'餎'
union all select 'lei',N'脷' --嘞脷
union all select 'leng',N'睖'
union all select 'li',N'瓈'
union all select 'lia',N'倆'
union all select 'lian',N'纞'
union all select 'liang',N'鍄'
union all select 'liao',N'瞭'
union all select 'lie',N'鱲'
union all select 'lin',N'轥' --轥拎
union all select 'ling',N'炩'
union all select 'liu',N'咯' --瓼甅囖咯
union all select 'long',N'贚'
union all select 'lou',N'鏤'
union all select 'lu',N'氇'
union all select 'lv',N'鑢'
union all select 'luan',N'亂'
union all select 'lue',N'擽'
union all select 'lun',N'論'
union all select 'luo',N'鱳'
union all select 'ma',N'嘛'
union all select 'mai',N'霢'
union all select 'man',N'蘰'
union all select 'mang',N'蠎'
union all select 'mao',N'唜'
union all select 'me',N'癦' --癦呅
union all select 'mei',N'嚜'
union all select 'men',N'們'
union all select 'meng',N'霥' --霿踎
union all select 'mi',N'羃'
union all select 'mian',N'麵'
union all select 'miao',N'廟'
union all select 'mie',N'鱴' --鱴瓱
union all select 'min',N'鰵'
union all select 'ming',N'詺'
union all select 'miu',N'謬'
union all select 'mo',N'耱' --耱乮
union all select 'mou',N'麰' --麰蟱
union all select 'mu',N'旀'
union all select 'na',N'魶'
union all select 'nai',N'錼'
union all select 'nan',N'婻'
union all select 'nang',N'齉'
union all select 'nao',N'臑'
union all select 'ne',N'呢'
union all select 'nei',N'焾' --嫩焾
union all select 'nen',N'嫩'
union all select 'neng',N'能' --莻嗯鈪銰啱
union all select 'ni',N'嬺'
union all select 'nian',N'艌'
union all select 'niang',N'釀'
union all select 'niao',N'脲'
union all select 'nie',N'钀'
union all select 'nin',N'拰'
union all select 'ning',N'濘'
union all select 'niu',N'靵'
union all select 'nong',N'齈'
union all select 'nou',N'譳'
union all select 'nu',N'搙'
union all select 'nv',N'衄'
union all select 'nue',N'瘧'
union all select 'nuan',N'燶' --硸黁燶郍
union all select 'nuo',N'桛'
union all select 'o',N'鞰' --毮夞乯鞰
union all select 'ou',N'漚'
union all select 'pa',N'袙'
union all select 'pai',N'磗' --鎃磗
union all select 'pan',N'鑻'
union all select 'pang',N'胖'
union all select 'pao',N'礮'
union all select 'pei',N'轡'
union all select 'pen',N'喯'
union all select 'peng',N'喸' --浌巼闏乶喸
union all select 'pi',N'鸊'
union all select 'pian',N'騙'
union all select 'piao',N'慓'
union all select 'pie',N'嫳'
union all select 'pin',N'聘'
union all select 'ping',N'蘋'
union all select 'po',N'魄'
union all select 'pou',N'哛' --兺哛
union all select 'pu',N'曝'
union all select 'qi',N'蟿'
union all select 'qia',N'髂'
union all select 'qian',N'縴'
union all select 'qiang',N'瓩' --羻兛瓩
union all select 'qiao',N'躈'
union all select 'qie',N'籡'
union all select 'qin',N'藽'
union all select 'qing',N'櫦'
union all select 'qiong',N'瓗'
union all select 'qiu',N'糗'
union all select 'qu',N'覻'
union all select 'quan',N'勸'
union all select 'que',N'礭'
union all select 'qun',N'囕'
union all select 'ran',N'橪'
union all select 'rang',N'讓'
union all select 'rao',N'繞'
union all select 're',N'熱'
union all select 'ren',N'餁'
union all select 'reng',N'陾'
union all select 'ri',N'馹'
union all select 'rong',N'穃'
union all select 'rou',N'嶿'
union all select 'ru',N'擩'
union all select 'ruan',N'礝'
union all select 'rui',N'壡'
union all select 'run',N'橍' --橍挼
union all select 'ruo',N'鶸'
union all select 'sa',N'栍' --櫒栍
union all select 'sai',N'虄' --簺虄
union all select 'san',N'閐'
union all select 'sang',N'喪'
union all select 'sao',N'髞'
union all select 'se',N'飋' --裇聓
union all select 'sen',N'篸'
union all select 'seng',N'縇' --閪縇
union all select 'sha',N'霎'
union all select 'shai',N'曬'
union all select 'shan',N'鱔'
union all select 'shang',N'緔'
union all select 'shao',N'潲'
union all select 'she',N'欇'
union all select 'shen',N'瘮'
union all select 'sheng',N'賸'
union all select 'shi',N'瓧' --鰘齛兙瓧
union all select 'shou',N'鏉'
union all select 'shu',N'虪'
union all select 'shua',N'誜'
union all select 'shuai',N'卛'
union all select 'shuan',N'腨'
union all select 'shuang',N'灀'
union all select 'shui',N'睡'
union all select 'shun',N'鬊'
union all select 'shuo',N'鑠'
union all select 'si',N'乺' --瀃螦乺
union all select 'song',N'鎹'
union all select 'sou',N'瘶'
union all select 'su',N'鷫'
union all select 'suan',N'算'
union all select 'sui',N'鐩'
union all select 'sun',N'潠'
union all select 'suo',N'蜶'
union all select 'ta',N'襨' --躢襨
union all select 'tai',N'燤'
union all select 'tan',N'賧'
union all select 'tang',N'燙'
union all select 'tao',N'畓' --討畓
union all select 'te',N'蟘'
union all select 'teng',N'朰' --霯唞朰
union all select 'ti',N'趯'
union all select 'tian',N'舚'
union all select 'tiao',N'糶'
union all select 'tie',N'餮'
union all select 'ting',N'乭' --濎乭
union all select 'tong',N'憅'
union all select 'tou',N'透'
union all select 'tu',N'鵵'
union all select 'tuan',N'褖'
union all select 'tui',N'駾'
union all select 'tun',N'坉'
union all select 'tuo',N'籜'
union all select 'wa',N'韤'
union all select 'wai',N'顡'
union all select 'wan',N'贎'
union all select 'wang',N'朢'
union all select 'wei',N'躛'
union all select 'wen',N'璺'
union all select 'weng',N'齆'
union all select 'wo',N'齷'
union all select 'wu',N'鶩'
union all select 'xi',N'衋'
union all select 'xia',N'鏬'
union all select 'xian',N'鼸'
union all select 'xiang',N'鱌'
union all select 'xiao',N'斆'
union all select 'xie',N'躞'
union all select 'xin',N'釁'
union all select 'xing',N'臖'
union all select 'xiong',N'敻'
union all select 'xiu',N'齅'
union all select 'xu',N'蓿'
union all select 'xuan',N'贙'
union all select 'xue',N'瀥'
union all select 'xun',N'鑂'
union all select 'ya',N'齾'
union all select 'yan',N'灩'
union all select 'yang',N'樣'
union all select 'yao',N'鑰'
union all select 'ye',N'岃' --鸈膶岃
union all select 'yi',N'齸'
union all select 'yin',N'檼'
union all select 'ying',N'譍'
union all select 'yo',N'喲'
union all select 'yong',N'醟'
union all select 'you',N'鼬'
union all select 'yu',N'爩'
union all select 'yuan',N'願'
union all select 'yue',N'鸙'
union all select 'yun',N'韻'
union all select 'za',N'雥'
union all select 'zai',N'縡'
union all select 'zan',N'饡'
union all select 'zang',N'臟'
union all select 'zao',N'竈'
union all select 'ze',N'稄'
union all select 'zei',N'鱡'
union all select 'zen',N'囎'
union all select 'zeng',N'贈'
union all select 'zha',N'醡'
union all select 'zhai',N'瘵'
union all select 'zhan',N'驏'
union all select 'zhang',N'瞕'
union all select 'zhao',N'羄'
union all select 'zhe',N'鷓'
union all select 'zhen',N'黮'
union all select 'zheng',N'證'
union all select 'zhi',N'豒'
union all select 'zhong',N'諥'
union all select 'zhou',N'驟'
union all select 'zhu',N'鑄'
union all select 'zhua',N'爪'
union all select 'zhuai',N'跩'
union all select 'zhuan',N'籑'
union all select 'zhuang',N'戅'
union all select 'zhui',N'鑆'
union all select 'zhun',N'稕'
union all select 'zhuo',N'籱'
union all select 'zi',N'漬' --漬唨
union all select 'zong',N'縱'
union all select 'zou',N'媰'
union all select 'zu',N'謯'
union all select 'zuan',N'攥'
union all select 'zui',N'欈'
union all select 'zun',N'銌'
union all select 'zuo',N'咗') t
where word >= @word collate Chinese_PRC_CS_AS_KS_WS
order by word collate Chinese_PRC_CS_AS_KS_WS ASC) else @word end)
set @i = @i + 1
end
return @pinyin
END 測試了一串字元這個案例同樣能達到漢字轉拼音要求,該解決方案還好沒讓我看到表插入,這回性能應該大有提高
我們來看那看它的執行開銷
果然不出所料,估計子樹大小在0.017左右, 其中排序開銷最大
我用一萬條資料測試該函數花了10秒左右
既然排序開銷最大那就優化該解決方案去掉order by。
這時估計子樹大小就在0.00001之下了，可你會發現此解決方案再怎麼優化還是免不了需要常量掃描,where篩選等運算.
我用一萬條資料測試該優化後函數花了4～5秒左右
出于對性能要求的不滿足,打開工具我自己寫了一個漢字轉拼音的函數
由于時間倉促未加任何注釋,有問題可及時提出
最後給出的就是重寫的解決方案
解決方案三
create function [dbo].[fn_Getquanpin3](@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000),@crs varchar(10)
declare @strlen int
select @strlen=len(@str),@re=''
while @strlen>0
begin
set @crs= substring(@str,@strlen,1)
select @re=
case
when @crs<'吖' then @crs
when @crs<='厑' then 'a'
when @crs<='靉' then 'ai'
when @crs<='黯' then 'an'
when @crs<='醠' then 'ang'
when @crs<='驁' then 'ao'
when @crs<='欛' then 'ba'
when @crs<='瓸' then 'bai'
when @crs<='瓣' then 'ban'
when @crs<='鎊' then 'bang'
when @crs<='鑤' then 'bao'
when @crs<='鐾' then 'bei'
when @crs<='輽' then 'ben'
when @crs<='鏰' then 'beng'
when @crs<='鼊' then 'bi'
when @crs<='變' then 'bian'
when @crs<='鰾' then 'biao'
when @crs<='彆' then 'bie'
when @crs<='鬢' then 'bin'
when @crs<='靐' then 'bing'
when @crs<='蔔' then 'bo'
when @crs<='簿' then 'bu'
when @crs<='囃' then 'ca'
when @crs<='乲' then 'cai'
when @crs<='爘' then 'can'
when @crs<='賶' then 'cang'
when @crs<='鼜' then 'cao'
when @crs<='簎' then 'ce'
when @crs<='笒' then 'cen'
when @crs<='乽' then 'ceng'
when @crs<='詫' then 'cha'
when @crs<='囆' then 'chai'
when @crs<='顫' then 'chan'
when @crs<='韔' then 'chang'
when @crs<='觘' then 'chao'
when @crs<='爡' then 'che'
when @crs<='讖' then 'chen'
when @crs<='秤' then 'cheng'
when @crs<='鷘' then 'chi'
when @crs<='銃' then 'chong'
when @crs<='殠' then 'chou'
when @crs<='矗' then 'chu'
when @crs<='踹' then 'chuai'
when @crs<='鶨' then 'chuan'
when @crs<='愴' then 'chuang'
when @crs<='顀' then 'chui'
when @crs<='蠢' then 'chun'
when @crs<='縒' then 'chuo'
when @crs<='嗭' then 'ci'
when @crs<='謥' then 'cong'
when @crs<='輳' then 'cou'
when @crs<='顣' then 'cu'
when @crs<='爨' then 'cuan'
when @crs<='臎' then 'cui'
when @crs<='籿' then 'cun'
when @crs<='錯' then 'cuo'
when @crs<='橽' then 'da'
when @crs<='靆' then 'dai'
when @crs<='饏' then 'dan'
when @crs<='闣' then 'dang'
when @crs<='纛' then 'dao'
when @crs<='的' then 'de'
when @crs<='扽' then 'den'
when @crs<='鐙' then 'deng'
when @crs<='螮' then 'di'
when @crs<='嗲' then 'dia'
when @crs<='驔' then 'dian'
when @crs<='鑃' then 'diao'
when @crs<='嚸' then 'die'
when @crs<='顁' then 'ding'
when @crs<='銩' then 'diu'
when @crs<='霘' then 'dong'
when @crs<='鬭' then 'dou'
when @crs<='蠹' then 'du'
when @crs<='叾' then 'duan'
when @crs<='譵' then 'dui'
when @crs<='踲' then 'dun'
when @crs<='鵽' then 'duo'
when @crs<='鱷' then 'e'
when @crs<='摁' then 'en'
when @crs<='鞥' then 'eng'
when @crs<='樲' then 'er'
when @crs<='髮' then 'fa'
when @crs<='瀪' then 'fan'
when @crs<='放' then 'fang'
when @crs<='靅' then 'fei'
when @crs<='鱝' then 'fen'
when @crs<='覅' then 'feng'
when @crs<='梻' then 'fo'
when @crs<='鴀' then 'fou'
when @crs<='猤' then 'fu'
when @crs<='魀' then 'ga'
when @crs<='瓂' then 'gai'
when @crs<='灨' then 'gan'
when @crs<='戇' then 'gang'
when @crs<='鋯' then 'gao'
when @crs<='獦' then 'ge'
when @crs<='給' then 'gei'
when @crs<='搄' then 'gen'
when @crs<='堩' then 'geng'
when @crs<='兣' then 'gong'
when @crs<='購' then 'gou'
when @crs<='顧' then 'gu'
when @crs<='詿' then 'gua'
when @crs<='恠' then 'guai'
when @crs<='鱹' then 'guan'
when @crs<='撗' then 'guang'
when @crs<='鱥' then 'gui'
when @crs<='謴' then 'gun'
when @crs<='腂' then 'guo'
when @crs<='哈' then 'ha'
when @crs<='饚' then 'hai'
when @crs<='鶾' then 'han'
when @crs<='沆' then 'hang'
when @crs<='兞' then 'hao'
when @crs<='靏' then 'he'
when @crs<='嬒' then 'hei'
when @crs<='恨' then 'hen'
when @crs<='堼' then 'heng'
when @crs<='鬨' then 'hong'
when @crs<='鱟' then 'hou'
when @crs<='鸌' then 'hu'
when @crs<='蘳' then 'hua'
when @crs<='蘾' then 'huai'
when @crs<='鰀' then 'huan'
when @crs<='鎤' then 'huang'
when @crs<='顪' then 'hui'
when @crs<='諢' then 'hun'
when @crs<='夻' then 'huo'
when @crs<='驥' then 'ji'
when @crs<='嗧' then 'jia'
when @crs<='鑳' then 'jian'
when @crs<='謽' then 'jiang'
when @crs<='釂' then 'jiao'
when @crs<='繲' then 'jie'
when @crs<='齽' then 'jin'
when @crs<='竸' then 'jing'
when @crs<='蘔' then 'jiong'
when @crs<='欍' then 'jiu'
when @crs<='爠' then 'ju'
when @crs<='羂' then 'juan'
when @crs<='钁' then 'jue'
when @crs<='攈' then 'jun'
when @crs<='鉲' then 'ka'
when @crs<='乫' then 'kai'
when @crs<='矙' then 'kan'
when @crs<='閌' then 'kang'
when @crs<='鯌' then 'kao'
when @crs<='騍' then 'ke'
when @crs<='褃' then 'ken'
when @crs<='鏗' then 'keng'
when @crs<='廤' then 'kong'
when @crs<='鷇' then 'kou'
when @crs<='嚳' then 'ku'
when @crs<='骻' then 'kua'
when @crs<='鱠' then 'kuai'
when @crs<='窾' then 'kuan'
when @crs<='鑛' then 'kuang'
when @crs<='鑎' then 'kui'
when @crs<='睏' then 'kun'
when @crs<='穒' then 'kuo'
when @crs<='鞡' then 'la'
when @crs<='籟' then 'lai'
when @crs<='糷' then 'lan'
when @crs<='唥' then 'lang'
when @crs<='軂' then 'lao'
when @crs<='餎' then 'le'
when @crs<='脷' then 'lei'
when @crs<='睖' then 'leng'
when @crs<='瓈' then 'li'
when @crs<='倆' then 'lia'
when @crs<='纞' then 'lian'
when @crs<='鍄' then 'liang'
when @crs<='瞭' then 'liao'
when @crs<='鱲' then 'lie'
when @crs<='轥' then 'lin'
when @crs<='炩' then 'ling'
when @crs<='咯' then 'liu'
when @crs<='贚' then 'long'
when @crs<='鏤' then 'lou'
when @crs<='氇' then 'lu'
when @crs<='鑢' then 'lv'
when @crs<='亂' then 'luan'
when @crs<='擽' then 'lue'
when @crs<='論' then 'lun'
when @crs<='鱳' then 'luo'
when @crs<='嘛' then 'ma'
when @crs<='霢' then 'mai'
when @crs<='蘰' then 'man'
when @crs<='蠎' then 'mang'
when @crs<='唜' then 'mao'
when @crs<='癦' then 'me'
when @crs<='嚜' then 'mei'
when @crs<='們' then 'men'
when @crs<='霥' then 'meng'
when @crs<='羃' then 'mi'
when @crs<='麵' then 'mian'
when @crs<='廟' then 'miao'
when @crs<='鱴' then 'mie'
when @crs<='鰵' then 'min'
when @crs<='詺' then 'ming'
when @crs<='謬' then 'miu'
when @crs<='耱' then 'mo'
when @crs<='麰' then 'mou'
when @crs<='旀' then 'mu'
when @crs<='魶' then 'na'
when @crs<='錼' then 'nai'
when @crs<='婻' then 'nan'
when @crs<='齉' then 'nang'
when @crs<='臑' then 'nao'
when @crs<='呢' then 'ne'
when @crs<='焾' then 'nei'
when @crs<='嫩' then 'nen'
when @crs<='能' then 'neng'
when @crs<='嬺' then 'ni'
when @crs<='艌' then 'nian'
when @crs<='釀' then 'niang'
when @crs<='脲' then 'niao'
when @crs<='钀' then 'nie'
when @crs<='拰' then 'nin'
when @crs<='濘' then 'ning'
when @crs<='靵' then 'niu'
when @crs<='齈' then 'nong'
when @crs<='譳' then 'nou'
when @crs<='搙' then 'nu'
when @crs<='衄' then 'nv'
when @crs<='瘧' then 'nue'
when @crs<='燶' then 'nuan'
when @crs<='桛' then 'nuo'
when @crs<='鞰' then 'o'
when @crs<='漚' then 'ou'
when @crs<='袙' then 'pa'
when @crs<='磗' then 'pai'
when @crs<='鑻' then 'pan'
when @crs<='胖' then 'pang'
when @crs<='礮' then 'pao'
when @crs<='轡' then 'pei'
when @crs<='喯' then 'pen'
when @crs<='喸' then 'peng'
when @crs<='鸊' then 'pi'
when @crs<='騙' then 'pian'
when @crs<='慓' then 'piao'
when @crs<='嫳' then 'pie'
when @crs<='聘' then 'pin'
when @crs<='蘋' then 'ping'
when @crs<='魄' then 'po'
when @crs<='哛' then 'pou'
when @crs<='曝' then 'pu'
when @crs<='蟿' then 'qi'
when @crs<='髂' then 'qia'
when @crs<='縴' then 'qian'
when @crs<='瓩' then 'qiang'
when @crs<='躈' then 'qiao'
when @crs<='籡' then 'qie'
when @crs<='藽' then 'qin'
when @crs<='櫦' then 'qing'
when @crs<='瓗' then 'qiong'
when @crs<='糗' then 'qiu'
when @crs<='覻' then 'qu'
when @crs<='勸' then 'quan'
when @crs<='礭' then 'que'
when @crs<='囕' then 'qun'
when @crs<='橪' then 'ran'
when @crs<='讓' then 'rang'
when @crs<='繞' then 'rao'
when @crs<='熱' then 're'
when @crs<='餁' then 'ren'
when @crs<='陾' then 'reng'
when @crs<='馹' then 'ri'
when @crs<='穃' then 'rong'
when @crs<='嶿' then 'rou'
when @crs<='擩' then 'ru'
when @crs<='礝' then 'ruan'
when @crs<='壡' then 'rui'
when @crs<='橍' then 'run'
when @crs<='鶸' then 'ruo'
when @crs<='栍' then 'sa'
when @crs<='虄' then 'sai'
when @crs<='閐' then 'san'
when @crs<='喪' then 'sang'
when @crs<='髞' then 'sao'
when @crs<='飋' then 'se'
when @crs<='篸' then 'sen'
when @crs<='縇' then 'seng'
when @crs<='霎' then 'sha'
when @crs<='曬' then 'shai'
when @crs<='鱔' then 'shan'
when @crs<='緔' then 'shang'
when @crs<='潲' then 'shao'
when @crs<='欇' then 'she'
when @crs<='瘮' then 'shen'
when @crs<='賸' then 'sheng'
when @crs<='瓧' then 'shi'
when @crs<='鏉' then 'shou'
when @crs<='虪' then 'shu'
when @crs<='誜' then 'shua'
when @crs<='卛' then 'shuai'
when @crs<='腨' then 'shuan'
when @crs<='灀' then 'shuang'
when @crs<='睡' then 'shui'
when @crs<='鬊' then 'shun'
when @crs<='鑠' then 'shuo'
when @crs<='乺' then 'si'
when @crs<='鎹' then 'song'
when @crs<='瘶' then 'sou'
when @crs<='鷫' then 'su'
when @crs<='算' then 'suan'
when @crs<='鐩' then 'sui'
when @crs<='潠' then 'sun'
when @crs<='蜶' then 'suo'
when @crs<='襨' then 'ta'
when @crs<='燤' then 'tai'
when @crs<='賧' then 'tan'
when @crs<='燙' then 'tang'
when @crs<='畓' then 'tao'
when @crs<='蟘' then 'te'
when @crs<='朰' then 'teng'
when @crs<='趯' then 'ti'
when @crs<='舚' then 'tian'
when @crs<='糶' then 'tiao'
when @crs<='餮' then 'tie'
when @crs<='乭' then 'ting'
when @crs<='憅' then 'tong'
when @crs<='透' then 'tou'
when @crs<='鵵' then 'tu'
when @crs<='褖' then 'tuan'
when @crs<='駾' then 'tui'
when @crs<='坉' then 'tun'
when @crs<='籜' then 'tuo'
when @crs<='韤' then 'wa'
when @crs<='顡' then 'wai'
when @crs<='贎' then 'wan'
when @crs<='朢' then 'wang'
when @crs<='躛' then 'wei'
when @crs<='璺' then 'wen'
when @crs<='齆' then 'weng'
when @crs<='齷' then 'wo'
when @crs<='鶩' then 'wu'
when @crs<='衋' then 'xi'
when @crs<='鏬' then 'xia'
when @crs<='鼸' then 'xian'
when @crs<='鱌' then 'xiang'
when @crs<='斆' then 'xiao'
when @crs<='躞' then 'xie'
when @crs<='釁' then 'xin'
when @crs<='臖' then 'xing'
when @crs<='敻' then 'xiong'
when @crs<='齅' then 'xiu'
when @crs<='蓿' then 'xu'
when @crs<='贙' then 'xuan'
when @crs<='瀥' then 'xue'
when @crs<='鑂' then 'xun'
when @crs<='齾' then 'ya'
when @crs<='灩' then 'yan'
when @crs<='樣' then 'yang'
when @crs<='鑰' then 'yao'
when @crs<='岃' then 'ye'
when @crs<='齸' then 'yi'
when @crs<='檼' then 'yin'
when @crs<='譍' then 'ying'
when @crs<='喲' then 'yo'
when @crs<='醟' then 'yong'
when @crs<='鼬' then 'you'
when @crs<='爩' then 'yu'
when @crs<='願' then 'yuan'
when @crs<='鸙' then 'yue'
when @crs<='韻' then 'yun'
when @crs<='雥' then 'za'
when @crs<='縡' then 'zai'
when @crs<='饡' then 'zan'
when @crs<='臟' then 'zang'
when @crs<='竈' then 'zao'
when @crs<='稄' then 'ze'
when @crs<='鱡' then 'zei'
when @crs<='囎' then 'zen'
when @crs<='贈' then 'zeng'
when @crs<='醡' then 'zha'
when @crs<='瘵' then 'zhai'
when @crs<='驏' then 'zhan'
when @crs<='瞕' then 'zhang'
when @crs<='羄' then 'zhao'
when @crs<='鷓' then 'zhe'
when @crs<='黮' then 'zhen'
when @crs<='證' then 'zheng'
when @crs<='豒' then 'zhi'
when @crs<='諥' then 'zhong'
when @crs<='驟' then 'zhou'
when @crs<='鑄' then 'zhu'
when @crs<='爪' then 'zhua'
when @crs<='跩' then 'zhuai'
when @crs<='籑' then 'zhuan'
when @crs<='戅' then 'zhuang'
when @crs<='鑆' then 'zhui'
when @crs<='稕' then 'zhun'
when @crs<='籱' then 'zhuo'
when @crs<='漬' then 'zi'
when @crs<='縱' then 'zong'
when @crs<='媰' then 'zou'
when @crs<='謯' then 'zu'
when @crs<='攥' then 'zuan'
when @crs<='欈' then 'zui'
when @crs<='銌' then 'zun'
when @crs<='咗' then 'zuo'
else @crs end+' '[email protected],@[email protected]
end
return(@re)
end 我們來看它的執行計劃
沒有排序,沒有常量掃描,沒有篩選器,沒有計算标量
估計子樹大小為0
我用一萬行測試資料花0~2秒就把所有漢字轉換為拼音
------------------------------------------------------------------------------
好了,文章就到這裡,快要過年了,祝各位園友們新年快樂。
追索
http://www.cnblogs.com/zhuisuo/archive/2012/01/11/2318908.html