/*
統計一個表中某個字元出現最多的字母
*/
--建立資料測試環境
create table #tb(a varchar(200))
insert into #tb
select 'abcdasdlfjaslk;dfqwoieurwhft'
union all select 'a;sldkjfal;sopqwhtdlkdafrfgsgasdfh'
union all select 'asldfkworufgsea87y9oqwpe tchgwccmnqnw3 '
--為字元分拆準備臨時表,top 200 是根據要分拆的字元串的最大長度而定的,
--在測試中,因為a字段的最大為200,故這裡用 top 200
select top 200 id=identity(int,1,1)
into #aa from
(select top 100 id from syscolumns) a
,(select top 100 id from syscolumns) b
,(select top 100 id from syscolumns) c
--得到結果
select top 1 substring(a,b.id,1)
from #tb a,#aa b
where substring(a.a,b.id,1)<>''
group by substring(a.a,b.id,1)
order by sum(1) desc
drop table #tb,#aa
chaunceyhao