34,590
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:@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 行受影响)
*/
---测试数据---
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 行)
where 用户 in('移动','联通')
where 用户 in(select 用户 from @tableA where len(号码)=3 )