以前三位数字分组,统计各组次数

Question

tableA
号码         用户
139          移动
13911111111  张一
13922222222  张二
13933333333  张三
13944444444  张四
130          联通
13011111111  赵一
13022222222  赵二
13033333333  赵三
13044444444  赵四tableB
号码            通话时长   主叫被叫
139111111111     10         主叫
139333333333     5          主叫
139111111111     10         被叫
139222222222      5         被叫
130111111111     10         主叫
130222222222     10         被叫
130333333333      5         被叫
130444444444     10         主叫想要的结果(以前三位分组,统计该组内的呼叫次数以比率)
号码   用户 通话次数   主叫次数  被叫次数  主叫率
139    移动   4          2         2         50%
130    联通   4          2         2         50%

Answer 1

在tableA中,有一条记录,139的名称是移动,130的名称是联通.也就是用户号码取出前三位后,再去找这三位对应的tableA中的名字.

Answer 2

--> 测试数据：@tableA
declare @tableA table([号码] bigint,[用户] varchar(4))
insert @tableA
select 139,'移动' union all
select 13911111111,'张一' union all
select 13922222222,'张二' union all
select 13933333333,'张三' union all
select 13944444444,'张四' union all
select 130,'联通' union all
select 13011111111,'赵一' union all
select 13022222222,'赵二' union all
select 13033333333,'赵三' union all
select 13044444444,'赵四'--> 测试数据：@tableB
declare @tableB table([号码] bigint,[通话时长] int,[主叫被叫] varchar(4))
insert @tableB
select 139111111111,10,'主叫' union all
select 139333333333,5,'主叫' union all
select 139111111111,10,'被叫' union all
select 139222222222,5,'被叫' union all
select 130111111111,10,'主叫' union all
select 130222222222,10,'被叫' union all
select 130333333333,5,'被叫' union all
select 130444444444,10,'主叫'select left(a.[号码],3),a.用户,
通话次数=count(*),
主叫次数=sum(case b.[主叫被叫] when '主叫' then 1 else 0 end),
被叫次数=sum(case b.[主叫被叫] when '被叫' then 1 else 0 end),
主叫率=ltrim(sum(case b.[主叫被叫] when '主叫' then 1 else 0 end)*100/count(*))+'%'
from @tableA a 
left join @tableB b on left(a.[号码],3)=left(b.[号码],3)
where 用户 in('移动','联通')
group by left(a.[号码],3),a.用户
/*
       用户   通话次数        主叫次数        被叫次数        主叫率
------ ---- ----------- ----------- ----------- -------------
130    联通   4           2           2           50%
139    移动   4           2           2           50%(2 行受影响)*/

Answer 3

主叫率=ltrim(cast(sum(case b.[主叫被叫] when '主叫' then 1 else 0 end)*100.0/count(*) as dec(18,2))+'%'

Answer 4

where 用户 in('移动','联通'),移动和联通只是我众多类别中的两种,如果我有几十种怎么办?必须象这样一一列出吗?有没有简单的办法?

Answer 5

用case when 判断前3位

Answer 6

tableA
号码         用户
139          移动
13911111111  张一
13922222222  张二
13933333333  张三
13944444444  张四
130          联通
13011111111  赵一
13022222222  赵二
13033333333  赵三
13044444444  赵四
137          移动A
13711111111  李一
13722222222  李二
136          移动B
13611111111  王一
13622222222  王二
135          移动C
13511111111  赵一
13522222222  赵二tableB
号码            通话时长   主叫被叫
139111111111     10         主叫
139333333333     5          主叫
139111111111     10         被叫
139222222222      5         被叫
130111111111     10         主叫
130222222222     10         被叫
130333333333      5         被叫
130444444444     10         主叫
135111111111     10         主叫
137111111111     5          被叫
想要的结果(以前三位分组,统计该组内的呼叫次数以比率)
号码   用户 通话次数   主叫次数  被叫次数  主叫率
139    移动   4          2         2         50%
130    联通   4          2         2         50%
135    移动C  1          1         0         100%
137    移动A  1          0         1         0%

Answer 7

把 where 用户 in('移动','联通')改为： where 用户 in(select 用户 from @tableA where len(号码)=3 )

Answer 8

---测试数据---
if object_id('[tableA]') is not null drop table [tableA]
go
create table [tableA]([号码] bigint,[用户] varchar(5))
insert [tableA]
select 139,'移动' union all
select 13911111111,'张一' union all
select 13922222222,'张二' union all
select 13933333333,'张三' union all
select 13944444444,'张四' union all
select 130,'联通' union all
select 13011111111,'赵一' union all
select 13022222222,'赵二' union all
select 13033333333,'赵三' union all
select 13044444444,'赵四' union all
select 137,'移动A' union all
select 13711111111,'李一' union all
select 13722222222,'李二' union all
select 136,'移动B' union all
select 13611111111,'王一' union all
select 13622222222,'王二' union all
select 135,'移动C' union all
select 13511111111,'赵一' union all
select 13522222222,'赵二'
go
if object_id('[tableB]') is not null drop table [tableB]
go
create table [tableB]([号码] bigint,[通话时长] int,[主叫被叫] varchar(4))
insert [tableB]
select 139111111111,10,'主叫' union all
select 139333333333,5,'主叫' union all
select 139111111111,10,'被叫' union all
select 139222222222,5,'被叫' union all
select 130111111111,10,'主叫' union all
select 130222222222,10,'被叫' union all
select 130333333333,5,'被叫' union all
select 130444444444,10,'主叫' union all
select 135111111111,10,'主叫' union all
select 137111111111,5,'被叫'
go
 
---查询---
select 
left(a.[号码],3) as 号码,
a.用户,
通话次数=count(*),
主叫次数=sum(case b.[主叫被叫] when '主叫' then 1 else 0 end),
被叫次数=sum(case b.[主叫被叫] when '被叫' then 1 else 0 end),
主叫率=ltrim(cast(sum(case b.[主叫被叫] when '主叫' then 1 else 0 end)*100.0/count(*) as dec(18,2)))+'%'
from tableA a 
left join tableB b on a.[号码]=left(b.[号码],3)
where len(a.号码)=3
group by a.[号码],a.用户---结果---
号码     用户    通话次数        主叫次数        被叫次数        主叫率                                       
------ ----- ----------- ----------- ----------- ----------------------------------------- 
130    联通    4           2           2           50.00%
139    移动    4           2           2           50.00%
137    移动A   1           0           1           0.00%
136    移动B   1           0           0           0.00%
135    移动C   1           1           0           100.00%（所影响的行数为 5 行）
这样？

Answer 9

declare @tableA table([号码] varchar(20),[用户] varchar(10))
insert @tableA
select 139,'移动' union all
select 13911111111,'张一' union all
select 13922222222,'张二' union all
select 13933333333,'张三' union all
select 13944444444,'张四' union all
select 130,'联通' union all
select 13011111111,'赵一' union all
select 13022222222,'赵二' union all
select 13033333333,'赵三' union all
select 13044444444,'赵四' union all
select 137,'移动A' union all
select 13711111111,'李一' union all
select 13722222222,'李二' union all
select 136,'移动B' union all
select 13611111111,'王一' union all
select 13622222222,'王二' union all
select 135,'移动C' union all
select 13511111111,'赵一' union all
select 13522222222,'赵二'
--> 测试数据：@tableB
declare @tableB table([号码] varchar(20),[通话时长] int,[主叫被叫] varchar(4))
insert @tableB
select 139111111111,10,'主叫' union all
select 139333333333,5,'主叫' union all
select 139111111111,10,'被叫' union all
select 139222222222,5,'被叫' union all
select 130111111111,10,'主叫' union all
select 130222222222,10,'被叫' union all
select 130333333333,5,'被叫' union all
select 130444444444,10,'主叫' union all
select 135111111111,10,'主叫' union all
select 137111111111,5,'被叫'select a.[号码],a.用户,
通话次数=count(*),
主叫次数=sum(case b.[主叫被叫] when '主叫' then 1 else 0 end),
被叫次数=sum(case b.[主叫被叫] when '被叫' then 1 else 0 end),
主叫率=ltrim(cast(sum(case b.[主叫被叫] when '主叫' then 1 else 0 end)*100.0/count(*) as dec(18,2)))+'%'
from @tableA a 
left join @tableB b on b.[号码] like a.[号码]+'%'
where len(a.[号码])=3
group by a.[号码],a.用户