求助：group by 语句
主叫 被叫
aa 17909-22
aa 22
aa 3317909
aa 17909-3317909
aa 17909-333
aa 333
aa 3333
aa 17909-3333
得到
主叫 被叫 拨打次数
aa 222
aa 33179092
aa 3332
aa 33332
create 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 '
谢谢！
[解决办法]
--try
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- ', ' ')
[解决办法]
同上
[解决办法]
create 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 '
select 主叫,substring(被叫,1,6) as 被叫, 拨打次数=count(*)
from bb
group by 主叫,substring(被叫,1,6)
[解决办法]
create table bb
(主叫 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 222
aa 33179092
aa 3332
aa 33332
[解决办法]
select 主叫,case when left(被叫,len( '17909 '))= '17909 ' then right(被叫,len(被叫)-len( '17909 ')-1) else 被叫 end,count(*)
from tbb
group by 主叫,case when left(被叫,len( '17909 '))= '17909 ' then right(被叫,len(被叫)-len( '17909 ')-1) else 被叫 end