主叫 被叫
aa 17909-22
aa 22
aa 3317909
aa 17909-3317909
aa 17909-333
aa 333
aa 3333
aa 17909-3333 得到主叫 被叫 拨打次数
aa 22 2
aa 3317909 2
aa 333 2
aa 3333 2create table bb
(主叫 char(10),
被叫 char(20))insert into bb
select 'aa ' , '17909-22'
union all
select 'aa ' , '22'
union all
select 'aa' , '3317909'
union all
select 'aa' ,'17909-3317909'
union all
select 'aa' ,'17909-333'
union all
select 'aa' ,'333'
union all
select 'aa' ,'3333'
union all
select 'aa' ,'17909-3333'
谢谢!
create table bb
(主叫 varchar(10),
被叫 varchar(20))insert into bb
select 'aa ' , '17909-22'
union all
select 'aa ' , '22'
union all
select 'aa' , '3317909'
union all
select 'aa' ,'17909-3317909'
union all
select 'aa' ,'17909-333'
union all
select 'aa' ,'333'
union all
select 'aa' ,'3333'
union all
select 'aa' ,'17909-3333'
select 主叫,replace(被叫,'17909-','') as 被叫, 拨打次数=count(*)
from bb
group by 主叫,replace(被叫,'17909-','')
aa 1790922
aa 22
aa 3317909
aa 179093317909
aa 17909333
aa 333
aa 3333
aa 179093333 得到主叫 被叫 拨打次数
aa 22 2
aa 3317909 2
aa 333 2
aa 3333 2
(主叫 char(10),
被叫 char(20))insert into bb
select 'aa ' , '17909-22'
union all
select 'aa ' ,'22'
union all
select 'aa' ,'3317909'
union all
select 'aa' ,'17909-3317909'
union all
select 'aa' ,'17909-333'
union all
select 'aa' ,'333'
union all
select 'aa' ,'3333'
union all
select 'aa' ,'17909-3333'select 主叫,substring(被叫,1,6) as 被叫, 拨打次数=count(*)
from bb
group by 主叫,substring(被叫,1,6)
(主叫 char(10),
被叫 char(20))insert into bb
select 'aa ' , '1790922'
union all
select 'aa ' ,'22'
union all
select 'aa' ,'3317909'
union all
select 'aa' ,'179093317909'
union all
select 'aa' ,'17909333'
union all
select 'aa' ,'333'
union all
select 'aa' ,'3333'
union all
select 'aa' ,'179093333'select 主叫,被叫,被叫次数=count(被叫) from (
select 主叫,被叫=case left(被叫,5)='17909' when substring(被叫,1,5) else 被叫 end
from bb
) t group by 主叫,被叫得到主叫 被叫 拨打次数
aa 22 2
aa 3317909 2
aa 333 2
aa 3333 2
from tbb
group by 主叫,case when left(被叫,len('17909'))='17909' then right(被叫,len(被叫)-len('17909')-1) else 被叫 end
aa 1790922
aa 22
aa 3317909
aa 179093317909
aa 17909333
aa 333
aa 3333
aa 179093333 得到主叫 被叫 拨打次数
aa 222
aa 33179092
aa 3332
aa 33332解决不过是:
select 主叫,被叫,被叫次数=count(被叫) from (
select 主叫,被叫=case when left(被叫,5)='17909' then stuff(被叫,1,5,'') else 被叫 end
from bb
) t group by 主叫,被叫