create table t(id int , [name] nvarchar(10))insert t
select 1,N'李啊王' union all
select 2,N'王义' union all
select 3,N'张三' union all
select 1,N'李啊王' union all
select 2,N'王张' union all
select 3,N'李王' union all
select 1,N'李王' union all
select 2,N'王李王' union all
select 3,N'张三'除了姓之 其他的字出现的次数 按高到低排列
select 1,N'李啊王' union all
select 2,N'王义' union all
select 3,N'张三' union all
select 1,N'李啊王' union all
select 2,N'王张' union all
select 3,N'李王' union all
select 1,N'李王' union all
select 2,N'王李王' union all
select 3,N'张三'除了姓之 其他的字出现的次数 按高到低排列
select aa=right([name],len([name])-1) ,bb=count(1)
from t where len([name])=2
group by right([name],len([name])-1)
union all
select aa=right([name],len([name])-2),bb=count(1)
from t where len([name])=3
group by right([name],len([name])-2)
) g
group by aa
order by sum(bb) desc
select 1,N'李啊王' union all
select 2,N'王义' union all
select 3,N'张三' union all
select 1,N'李啊王' union all
select 2,N'王张' union all
select 3,N'李王' union all
select 1,N'李王' union all
select 2,N'王李王' union all
select 3,N'张三'select name , count(*) cnt from
(
select * from
(
select substring(name,2,1) as name from t
union all
select substring(name,3,1) as name from t
union all
select substring(name,4,1) as name from t
union all
select substring(name,5,1) as name from t
union all
select substring(name,6,1) as name from t
union all
select substring(name,7,1) as name from t
union all
select substring(name,8,1) as name from t
union all
select substring(name,9,1) as name from t
union all
select substring(name,10,1) as name from t
) m
where len(rtrim(name)) = 1 and name not in (select left(name,1) from t)
) n
group by name
order by cnt descdrop table t/*
name cnt
---- -----------
啊 2
三 2
义 1(所影响的行数为 3 行)
*/