1. 例如某天='2005-12-20':select count(1) from smsbox_in where in_time=(select distinct dateadd(dd,-3,'2005-12-20') from smsbox_in where message='Q') and message='T'2.select (select count(1) from smsbox_in where message='Q' and in_time='2005-12-20')/(select count(1) from smsbox_in where message='T' and in_time='2005-12-20') --没考虑除以0的问题3. 注册(message为T)乘以20%的积?什么意思
2)求某一天的取消率,就是某一天的取消人数除以当天的注册人数的百分比,用一个sql语句写出。
declare @smsbox_in table(spnumber varchar(10),mobile varchar(20),message varchar(5),in_time datetime) insert into @smsbox_in select '0755','12011111','T','2005-12-20' union all select '0755','12011111','T','2005-12-24' union all select '0755','12110000','Q','2005-12-10' union all select '0755','12011111','Q','2005-12-27' union all select '0755','12222222','T','2005-12-01' union all select '0755','12110000','T','2005-12-05' /* 1)其中T为注册标识,Q为取消标识,注册和取消都在同一个表中,现在一个问题就是查找某一天取消用户是否是三天前注册的用户,的总人数 主要是这个时间不知道用什么方法来计算 */ select count(mobile) from @smsbox_in A where exists (select 1 from @smsbox_in where mobile=A.mobile and message='Q' and datediff(day,in_time,A.in_time)=-3) and message='T'/* 2)求某一天的取消率,就是某一天的取消人数除以当天的注册人数,用一个sql语句写出。 */select 取消率=cast(cast(A.Q as decimal(6,4))/B.T*100 as varchar(20))+'%' from (select count(mobile) as Q from @smsbox_in where message='Q') A, (select count(mobile) as T from @smsbox_in where message='T') B /* 3)还有一个sql乘法就是用sql语句输出注册(message为T)乘以20%的积, */ select count(mobile)*0.2 as T from @smsbox_in where message='T'
1. select BthreeDayT=count(1) from #1 a where message='Q' and exists(Select 1 from #1 where mobile=a.mobile and message='T' and in_time<dateadd(dd,-3,a.in_time))
对不起,忘了测试数据 Create table #1(spnumber varchar(10),mobile varchar(20),message varchar(5),in_time datetime) insert into #1 select '0755','12011111','T','2005-12-20' union all select '0755','12011111','T','2005-12-24' union all select '0755','12110000','Q','2005-12-10' union all select '0755','12011111','Q','2005-12-27' union all select '0755','12222222','T','2005-12-01' union all select '0755','12110000','T','2005-12-05'1. select BthreeDayT=count(1) from #1 a where message='Q' and exists(Select 1 from #1 where mobile=a.mobile and message='T' and in_time<dateadd(dd,-3,a.in_time))
Create table #1(spnumber varchar(10),mobile varchar(20),message varchar(5),in_time datetime) insert into #1 select '0755','12011111','T','2005-12-20' union all select '0755','12011111','T','2005-12-24' union all select '0755','12110000','Q','2005-12-10' union all select '0755','12011111','Q','2005-12-27' union all select '0755','12222222','T','2005-12-01' union all select '0755','12110000','T','2005-12-05'2. select in_time=isnull(a.in_time,b.in_time), Rate=case when a.in_time=b.in_time then convert(char(3),cast(b.Num*1.0/a.num*100 as varchar))+'%' else '0%' end from (Select in_time,message='T',Num=count(1) from #1 where message='T' group by in_time)a full outer join (Select in_time,message='Q',Num=count(1) from #1 where message='Q' group by in_time )b on a.in_time=b.in_time
3(简单的) select in_time,message,(message*20%) as a from smsbox_in where message='T'
例如某天='2005-12-20':select count(1)
from smsbox_in
where in_time=(select distinct dateadd(dd,-3,'2005-12-20') from smsbox_in where message='Q') and message='T'2.select (select count(1) from smsbox_in where message='Q' and in_time='2005-12-20')/(select count(1) from smsbox_in where message='T' and in_time='2005-12-20')
--没考虑除以0的问题3.
注册(message为T)乘以20%的积?什么意思
insert into @smsbox_in select '0755','12011111','T','2005-12-20'
union all select '0755','12011111','T','2005-12-24'
union all select '0755','12110000','Q','2005-12-10'
union all select '0755','12011111','Q','2005-12-27'
union all select '0755','12222222','T','2005-12-01'
union all select '0755','12110000','T','2005-12-05'
/*
1)其中T为注册标识,Q为取消标识,注册和取消都在同一个表中,现在一个问题就是查找某一天取消用户是否是三天前注册的用户,的总人数
主要是这个时间不知道用什么方法来计算
*/
select count(mobile) from @smsbox_in A
where exists
(select 1 from @smsbox_in where mobile=A.mobile and message='Q' and datediff(day,in_time,A.in_time)=-3)
and message='T'/*
2)求某一天的取消率,就是某一天的取消人数除以当天的注册人数,用一个sql语句写出。
*/select 取消率=cast(cast(A.Q as decimal(6,4))/B.T*100 as varchar(20))+'%' from (select count(mobile) as Q from @smsbox_in where message='Q') A,
(select count(mobile) as T from @smsbox_in where message='T') B
/*
3)还有一个sql乘法就是用sql语句输出注册(message为T)乘以20%的积,
*/
select count(mobile)*0.2 as T from @smsbox_in where message='T'
select BthreeDayT=count(1) from #1 a
where message='Q' and
exists(Select 1 from #1 where mobile=a.mobile and message='T' and in_time<dateadd(dd,-3,a.in_time))
Create table #1(spnumber varchar(10),mobile varchar(20),message varchar(5),in_time datetime)
insert into #1 select '0755','12011111','T','2005-12-20'
union all select '0755','12011111','T','2005-12-24'
union all select '0755','12110000','Q','2005-12-10'
union all select '0755','12011111','Q','2005-12-27'
union all select '0755','12222222','T','2005-12-01'
union all select '0755','12110000','T','2005-12-05'1.
select BthreeDayT=count(1) from #1 a
where message='Q' and
exists(Select 1 from #1 where mobile=a.mobile and message='T' and in_time<dateadd(dd,-3,a.in_time))
insert into #1 select '0755','12011111','T','2005-12-20'
union all select '0755','12011111','T','2005-12-24'
union all select '0755','12110000','Q','2005-12-10'
union all select '0755','12011111','Q','2005-12-27'
union all select '0755','12222222','T','2005-12-01'
union all select '0755','12110000','T','2005-12-05'2.
select in_time=isnull(a.in_time,b.in_time),
Rate=case when a.in_time=b.in_time then convert(char(3),cast(b.Num*1.0/a.num*100 as varchar))+'%' else '0%' end from
(Select in_time,message='T',Num=count(1) from #1
where message='T' group by in_time)a
full outer join
(Select in_time,message='Q',Num=count(1) from #1
where message='Q' group by in_time
)b on a.in_time=b.in_time
select in_time,message,(message*20%) as a from smsbox_in where message='T'