基础有关问题：统计每一类id的个数_Sql Server

基础问题：统计每一类id的个数?

SQL code

/*统计每一类id的个数（同一类中，相同的id计数是为1。比如bank-123,bank-123在bank-%类中有两次，最终统计时其个数为1，不是2）*/create table #tb(id varchar(50))insert into #tb values('bank-123'),('bank-123'),('bank-45'),('bank-ABC'),('Auto-123'),('Auto-456'),('Auto-789'),('Media-abc'),('Media-abc')，('ab12345'）,('ab12348');/*结果：bank-%  3Auto-%  3Media-% 1ab%     2*/

------解决方案--------------------
create table #tb(id varchar(50))
insert into #tb values
('bank-123'),
('bank-123'),
('bank-45'),
('bank-ABC'),
('Auto-123'),
('Auto-456'),
('Auto-789'),
('Media-abc'),
('Media-abc'),
('ab12345'),
('ab12348');

with t
as(
select left(id,case when patindex('%[0-9]%',id)<>0
then patindex('%[0-9]%',id)-1 else CHARINDEX('-',id)-1 end)+'%' as id
from #tb
)
select id,COUNT(1) as times from t group by id

/*
id times
ab% 2
Auto-% 3
bank% 1
bank-% 3
Media% 2
*/

------解决方案--------------------

SQL code

select   left(id,case when charindex('-',id)>0 then charindex('-',id) else patindex('%[^a-z]%',id)-1 end)+'%',  count(distinct id)from #tbgroup by left(id,case when charindex('-',id)>0 then charindex('-',id) else patindex('%[^a-z]%',id)-1 end)