select sum(case when patindex('%[0-9]%',left(col,1))>0 then 1 else 0 end) as [数字], sum(case when patindex('%[a-z]%',left(col,1))>0 then 1 else 0 end) as [英文字母], sum(case when patindex('%[0-9]%',left(col,1))=0 and patindex('%[a-z]%',left(col,1))=0 then 1 else 0 end) as [其他] from tb
select id , sum(val) val from ( select case when left(ID,1) between 'a' and 'z' then 'a-z' when left(ID,1) between '0' and '9' then '0-9' else '其他' end id , val from tb ) t group by id
declare @sql varchar(8000) select @sql=isnull(@sql+',','')+' sum(case left(col,1) when '''+code+''' then 1 else 0 end) as ['+code+']' from (select char(number) as code from master..spt_values where type = 'P' and number between 65 and 90) tselect @sql = 'select sum(case when left(col,1) like ''[0-9]%'' then 1 else 0 end) as [数字],'+ @sql+ ', sum(case when patindex(''%[0-9]%'',left(col,1))=0 and patindex(''%[a-z]%'',left(col,1))=0) then 1 else 0 end) as [特殊字符] from [tb]' print @sql------------------------------------------- select sum(case when left(col,1) like '[0-9]%' then 1 else 0 end) as [数字], sum(case left(col,1) when 'A' then 1 else 0 end) as [A], sum(case left(col,1) when 'B' then 1 else 0 end) as [B], sum(case left(col,1) when 'C' then 1 else 0 end) as [C], sum(case left(col,1) when 'D' then 1 else 0 end) as [D], sum(case left(col,1) when 'E' then 1 else 0 end) as [E], sum(case left(col,1) when 'F' then 1 else 0 end) as [F], sum(case left(col,1) when 'G' then 1 else 0 end) as [G], sum(case left(col,1) when 'H' then 1 else 0 end) as [H], sum(case left(col,1) when 'I' then 1 else 0 end) as [I], sum(case left(col,1) when 'J' then 1 else 0 end) as [J], sum(case left(col,1) when 'K' then 1 else 0 end) as [K], sum(case left(col,1) when 'L' then 1 else 0 end) as [L], sum(case left(col,1) when 'M' then 1 else 0 end) as [M], sum(case left(col,1) when 'N' then 1 else 0 end) as [N], sum(case left(col,1) when 'O' then 1 else 0 end) as [O], sum(case left(col,1) when 'P' then 1 else 0 end) as [P], sum(case left(col,1) when 'Q' then 1 else 0 end) as [Q], sum(case left(col,1) when 'R' then 1 else 0 end) as [R], sum(case left(col,1) when 'S' then 1 else 0 end) as [S], sum(case left(col,1) when 'T' then 1 else 0 end) as [T], sum(case left(col,1) when 'U' then 1 else 0 end) as [U], sum(case left(col,1) when 'V' then 1 else 0 end) as [V], sum(case left(col,1) when 'W' then 1 else 0 end) as [W], sum(case left(col,1) when 'X' then 1 else 0 end) as [X], sum(case left(col,1) when 'Y' then 1 else 0 end) as [Y], sum(case left(col,1) when 'Z' then 1 else 0 end) as [Z], sum(case when patindex('%[0-9]%',left(col,1))=0 and patindex('%[a-z]%',left(col,1))=0) then 1 else 0 end) as [特殊字符] from [tb]
--or select col,count(1) as nums from (select case when left(col,1) like '[0-9]%' then '数字' when patindex('%[0-9]%',left(col,1))=0 and patindex('%[a-z]%',left(col,1))=0 then '特殊字符' else left(col,1) end as col from tb) t group by col
//感觉用 CHAR(),ASII(),LEFT() //加适当的判断很容易实现的
CHAR(),ASCII(),LEFT()拼错了~ ㅠㅠ
同意四楼的.select id , sum(val) val from ( select case when left(ID,1) between 'a' and 'z' then 'a-z' when left(ID,1) between '0' and '9' then '0-9' else '其他' end id , val from tb ) t group by id
sum(case when patindex('%[0-9]%',left(col,1))>0 then 1 else 0 end) as [数字],
sum(case when patindex('%[a-z]%',left(col,1))>0 then 1 else 0 end) as [英文字母],
sum(case when patindex('%[0-9]%',left(col,1))=0 and patindex('%[a-z]%',left(col,1))=0 then 1 else 0 end) as [其他]
from
tb
(
select case when left(ID,1) between 'a' and 'z' then 'a-z'
when left(ID,1) between '0' and '9' then '0-9'
else '其他'
end id , val
from tb
) t
group by id
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'
sum(case left(col,1) when '''+code+''' then 1 else 0 end) as ['+code+']'
from (select char(number) as code from master..spt_values where type = 'P' and number between 65 and 90) tselect @sql = 'select sum(case when left(col,1) like ''[0-9]%'' then 1 else 0 end) as [数字],'+
@sql+
',
sum(case when patindex(''%[0-9]%'',left(col,1))=0 and patindex(''%[a-z]%'',left(col,1))=0) then 1 else 0 end) as [特殊字符]
from [tb]'
print @sql-------------------------------------------
select sum(case when left(col,1) like '[0-9]%' then 1 else 0 end) as [数字],
sum(case left(col,1) when 'A' then 1 else 0 end) as [A],
sum(case left(col,1) when 'B' then 1 else 0 end) as [B],
sum(case left(col,1) when 'C' then 1 else 0 end) as [C],
sum(case left(col,1) when 'D' then 1 else 0 end) as [D],
sum(case left(col,1) when 'E' then 1 else 0 end) as [E],
sum(case left(col,1) when 'F' then 1 else 0 end) as [F],
sum(case left(col,1) when 'G' then 1 else 0 end) as [G],
sum(case left(col,1) when 'H' then 1 else 0 end) as [H],
sum(case left(col,1) when 'I' then 1 else 0 end) as [I],
sum(case left(col,1) when 'J' then 1 else 0 end) as [J],
sum(case left(col,1) when 'K' then 1 else 0 end) as [K],
sum(case left(col,1) when 'L' then 1 else 0 end) as [L],
sum(case left(col,1) when 'M' then 1 else 0 end) as [M],
sum(case left(col,1) when 'N' then 1 else 0 end) as [N],
sum(case left(col,1) when 'O' then 1 else 0 end) as [O],
sum(case left(col,1) when 'P' then 1 else 0 end) as [P],
sum(case left(col,1) when 'Q' then 1 else 0 end) as [Q],
sum(case left(col,1) when 'R' then 1 else 0 end) as [R],
sum(case left(col,1) when 'S' then 1 else 0 end) as [S],
sum(case left(col,1) when 'T' then 1 else 0 end) as [T],
sum(case left(col,1) when 'U' then 1 else 0 end) as [U],
sum(case left(col,1) when 'V' then 1 else 0 end) as [V],
sum(case left(col,1) when 'W' then 1 else 0 end) as [W],
sum(case left(col,1) when 'X' then 1 else 0 end) as [X],
sum(case left(col,1) when 'Y' then 1 else 0 end) as [Y],
sum(case left(col,1) when 'Z' then 1 else 0 end) as [Z],
sum(case when patindex('%[0-9]%',left(col,1))=0 and patindex('%[a-z]%',left(col,1))=0) then 1 else 0 end) as [特殊字符]
from [tb]
select col,count(1) as nums
from
(select case when left(col,1) like '[0-9]%' then '数字'
when patindex('%[0-9]%',left(col,1))=0 and patindex('%[a-z]%',left(col,1))=0 then '特殊字符'
else left(col,1) end as col from tb) t
group by col
CHAR(),ASII(),LEFT()
//加适当的判断很容易实现的
(
select case when left(ID,1) between 'a' and 'z' then 'a-z'
when left(ID,1) between '0' and '9' then '0-9'
else '其他'
end id , val
from tb
) t
group by id