proc 储过程
来源：互联网 发布：淘宝怎么修改评价内容 编辑：程序博客网 时间：2024/04/29 19:03
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--**分类报表存储过程
ALTER proc gnfl_proc
as
declare
@p_phone varchar(30), @p_sex int, @p_age int, @p_city varchar(50), @p_r_type int, @p_r_time datetime,
@v_ping int, --PING码上行总计
@v_ping_ejj int, --PING码上行其中EJJ用户数
@v_ping_bjj int, --PING码上行其中BJJ用户数
@v_ping_con int, --PING码上行中注册和未注册的用户(consumer)不含发送EJJ/BJJ用户
@v_ping_reg int, --发送PING码并注册的用户（含不在同一天注册的用户）
@v_ping_reg_ejj int, --发送PING码并注册且发送过EJJ的用户
@v_ping_reg_bjj int, --发送PING码并注册且发关过BJJ的用户
@v_ping_reg_con int, --发送PING码注册的用户(consumer)不含发送过EJJ/BJJ的用户
@v_ping_noreg int, --发送PING码未注册的用户
@v_ping_noreg_ejj int, --发送PING码未注册而且发送了EJJ的用户
@v_ping_noreg_bjj int, --发送PING码未注册而且发送了BJJ的用户
@v_ping_noreg_con int, --发送PING码未注册也示发送EJJ/BJJ的用户(consumer)]
@v_man int, --男性
@v_women int, --女性
@v_age20 int, --年龄在20岁以下
@v_age29 int, --年龄在20~29岁
@v_age39 int, --年龄在29~39岁
@v_age49 int, --年龄在39~49岁
@v_age50 int, --年龄在49岁以上
@v_citysh int, --上海
@v_citybj int, --北京
@v_cityhz int, --杭州
@v_citygz int, --广州
@v_citysz int, --深圳
@v_cityqt int, --其他城市
@v_ejj_user int, --EJJ用户 1
@v_bjj_user int, --BJJ用户 2
@v_coun int --写入临时表的判断条件
select
--统计男
@v_man=count(
case
when usersex='1' then 1 else null
end
),
--统计女
@v_women=count(
case
when usersex='2' then 1 else null
end
),
--统计20岁
@v_age20=count(case
when userAge< 20 then 1
else null
end
) ,
--统计29岁
@v_age29=count(case
when userAge< 30 and userAge>19 then 1
else null
end
),
--统计39岁
@v_age39=count(case
when userAge < 40 and userAge>29 then 1
else null
end),
--统计49岁
@v_age49=count(case
when userAge < 50 and userAge>39 then 1
else null
end),
--统计50岁
@v_age50=count(case
when userAge >49 then 1
else null
end),
--统计上海用户
@v_citysh=count(case
when city='上海' then 1
else null
end
) ,
--统计广州用户
@v_citygz=count(case
when city='广州' then 1
else null
end
),
--统计北京用户
@v_citybj=count(case
when city='北京' then 1
else null
end
),
--统计杭州用户
@v_cityhz=count(case
when city='杭州' then 1
else null
end
),
--统计深圳用户
@v_citysz=count(case
when city='深圳' then 1
else null
end
) ,
----统计除已上之外的其他用户
@v_cityqt=count(case
when city not in ('北京','广州','上海','杭州','深圳') then 1
else null
end
)
from (select *
from tb_gl_userInf
where registertype = 4
and usertype&4=4
and CONVERT(char(10), registertime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
union all
select *
from tb_gl_userInf
where registertype !=4
and usertype&4 = 4
and CONVERT(char(10), mulregtime,20) = CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
--统计ping码上行数据tb_gl_userreplycommand
--ping码上行总计
select @v_ping=count(*)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
--发送ping码且发送EJJ用户数
select @v_ping_ejj=count(*)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone from tb_gl_userreplycommand where motype = '2')
--发送ping码且发送BJJ用户数
select @v_ping_bjj=count(*)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone from tb_gl_userreplycommand where motype = '1');
--发送ping码且非EJJ/BJJ（consumer）用户
set @v_ping_con= @v_ping - @v_ping_ejj - @v_ping_bjj;
--发送ping码并注册的用户
select @v_ping_reg=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))
--发送ping码并注册且发送过EJJ的用户
select @v_ping_ejj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '2'))
--发送ping码并注册且发送过BJJ的用户
select @v_ping_reg_bjj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '1'))
--发送ping码并注册但不含EJJ/BJJ用户(consumer)
set @v_ping_reg_ejj=0
set @v_ping_reg_con= @v_ping_reg - @v_ping_reg_ejj - @v_ping_reg_bjj;
--发送ping码未注册的用户
select @v_ping_noreg=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone not in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))
--发送ping码未注册且发送过EJJ的用户
select @v_ping_noreg_ejj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '2'))
--发送ping码未注册且发送过BJJ的用户
select @v_ping_noreg_bjj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '1'))
--发送ping码未注册未发送过ejj/bjj的用户
set @v_ping_noreg_con= @v_ping_noreg - @v_ping_noreg_ejj - @v_ping_noreg_bjj;
--统计EJJ用户
select @v_ejj_user=count(*)
from tb_gl_userreplycommand
where moType = '2'
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
--统计BJJ用户
select @v_bjj_user=count(*)
from tb_gl_userreplycommand
where moType = '1'
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
-- select @v_coun =count(*) from t_gnhd_temp where datetime = CONVERT (char(10), getdate() ,20)
-- if @v_coun = 0
--begin
insert into tb_report_gnfl
(ping,
ping_ejj,
ping_bjj,
ping_con,
ping_reg,
ping_reg_ejj,
ping_reg_bjj,
ping_reg_con,
man,
women,
age20,
age29,
age39,
age49,
age50,
citysh,
citybj,
cityhz,
citygz,
citysz,
cityqt,
ping_noreg,
ping_noreg_ejj,
ping_noreg_bjj,
ping_noreg_con,
ejj_user,
bjj_user,
datetime)
values(
@v_ping,
@v_ping_ejj,
@v_ping_bjj,
@v_ping_con,
@v_ping_reg,
@v_ping_reg_ejj,
@v_ping_reg_bjj,
@v_ping_reg_con,
@v_man,
@v_women,
@v_age20,
@v_age29,
@v_age39,
@v_age49,
@v_age50,
@v_citysh,
@v_citybj,
@v_cityhz,
@v_citygz,
@v_citysz,
@v_cityqt,
@v_ping_noreg,
@v_ping_noreg_ejj,
@v_ping_noreg_bjj,
@v_ping_noreg_con,
@v_ejj_user,
@v_bjj_user,
CONVERT(char(10), dateadd(dd,-1,getdate()),20))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO
--**分类报表存储过程
ALTER proc gnfl_proc
as
declare
@p_phone varchar(30), @p_sex int, @p_age int, @p_city varchar(50), @p_r_type int, @p_r_time datetime,
@v_ping int, --PING码上行总计
@v_ping_ejj int, --PING码上行其中EJJ用户数
@v_ping_bjj int, --PING码上行其中BJJ用户数
@v_ping_con int, --PING码上行中注册和未注册的用户(consumer)不含发送EJJ/BJJ用户
@v_ping_reg int, --发送PING码并注册的用户（含不在同一天注册的用户）
@v_ping_reg_ejj int, --发送PING码并注册且发送过EJJ的用户
@v_ping_reg_bjj int, --发送PING码并注册且发关过BJJ的用户
@v_ping_reg_con int, --发送PING码注册的用户(consumer)不含发送过EJJ/BJJ的用户
@v_ping_noreg int, --发送PING码未注册的用户
@v_ping_noreg_ejj int, --发送PING码未注册而且发送了EJJ的用户
@v_ping_noreg_bjj int, --发送PING码未注册而且发送了BJJ的用户
@v_ping_noreg_con int, --发送PING码未注册也示发送EJJ/BJJ的用户(consumer)]
@v_man int, --男性
@v_women int, --女性
@v_age20 int, --年龄在20岁以下
@v_age29 int, --年龄在20~29岁
@v_age39 int, --年龄在29~39岁
@v_age49 int, --年龄在39~49岁
@v_age50 int, --年龄在49岁以上
@v_citysh int, --上海
@v_citybj int, --北京
@v_cityhz int, --杭州
@v_citygz int, --广州
@v_citysz int, --深圳
@v_cityqt int, --其他城市
@v_ejj_user int, --EJJ用户 1
@v_bjj_user int, --BJJ用户 2
@v_coun int --写入临时表的判断条件
select
--统计男
@v_man=count(
case
when usersex='1' then 1 else null
end
),
--统计女
@v_women=count(
case
when usersex='2' then 1 else null
end
),
--统计20岁
@v_age20=count(case
when userAge< 20 then 1
else null
end
) ,
--统计29岁
@v_age29=count(case
when userAge< 30 and userAge>19 then 1
else null
end
),
--统计39岁
@v_age39=count(case
when userAge < 40 and userAge>29 then 1
else null
end),
--统计49岁
@v_age49=count(case
when userAge < 50 and userAge>39 then 1
else null
end),
--统计50岁
@v_age50=count(case
when userAge >49 then 1
else null
end),
--统计上海用户
@v_citysh=count(case
when city='上海' then 1
else null
end
) ,
--统计广州用户
@v_citygz=count(case
when city='广州' then 1
else null
end
),
--统计北京用户
@v_citybj=count(case
when city='北京' then 1
else null
end
),
--统计杭州用户
@v_cityhz=count(case
when city='杭州' then 1
else null
end
),
--统计深圳用户
@v_citysz=count(case
when city='深圳' then 1
else null
end
) ,
----统计除已上之外的其他用户
@v_cityqt=count(case
when city not in ('北京','广州','上海','杭州','深圳') then 1
else null
end
)
from (select *
from tb_gl_userInf
where registertype = 4
and usertype&4=4
and CONVERT(char(10), registertime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
union all
select *
from tb_gl_userInf
where registertype !=4
and usertype&4 = 4
and CONVERT(char(10), mulregtime,20) = CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
--统计ping码上行数据tb_gl_userreplycommand
--ping码上行总计
select @v_ping=count(*)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
--发送ping码且发送EJJ用户数
select @v_ping_ejj=count(*)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone from tb_gl_userreplycommand where motype = '2')
--发送ping码且发送BJJ用户数
select @v_ping_bjj=count(*)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone from tb_gl_userreplycommand where motype = '1');
--发送ping码且非EJJ/BJJ（consumer）用户
set @v_ping_con= @v_ping - @v_ping_ejj - @v_ping_bjj;
--发送ping码并注册的用户
select @v_ping_reg=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))
--发送ping码并注册且发送过EJJ的用户
select @v_ping_ejj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '2'))
--发送ping码并注册且发送过BJJ的用户
select @v_ping_reg_bjj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '1'))
--发送ping码并注册但不含EJJ/BJJ用户(consumer)
set @v_ping_reg_ejj=0
set @v_ping_reg_con= @v_ping_reg - @v_ping_reg_ejj - @v_ping_reg_bjj;
--发送ping码未注册的用户
select @v_ping_noreg=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone not in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))
--发送ping码未注册且发送过EJJ的用户
select @v_ping_noreg_ejj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '2'))
--发送ping码未注册且发送过BJJ的用户
select @v_ping_noreg_bjj=count(distinct telephone)
from (select *
from tb_gl_userreplycommand
where motype = 3
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a
where telephone in
(select telephone
from tb_gl_userreplycommand
where motype = 4
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
and telephone in
(select telephone from tb_gl_userreplycommand where motype = '1'))
--发送ping码未注册未发送过ejj/bjj的用户
set @v_ping_noreg_con= @v_ping_noreg - @v_ping_noreg_ejj - @v_ping_noreg_bjj;
--统计EJJ用户
select @v_ejj_user=count(*)
from tb_gl_userreplycommand
where moType = '2'
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
--统计BJJ用户
select @v_bjj_user=count(*)
from tb_gl_userreplycommand
where moType = '1'
and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)
-- select @v_coun =count(*) from t_gnhd_temp where datetime = CONVERT (char(10), getdate() ,20)
-- if @v_coun = 0
--begin
insert into tb_report_gnfl
(ping,
ping_ejj,
ping_bjj,
ping_con,
ping_reg,
ping_reg_ejj,
ping_reg_bjj,
ping_reg_con,
man,
women,
age20,
age29,
age39,
age49,
age50,
citysh,
citybj,
cityhz,
citygz,
citysz,
cityqt,
ping_noreg,
ping_noreg_ejj,
ping_noreg_bjj,
ping_noreg_con,
ejj_user,
bjj_user,
datetime)
values(
@v_ping,
@v_ping_ejj,
@v_ping_bjj,
@v_ping_con,
@v_ping_reg,
@v_ping_reg_ejj,
@v_ping_reg_bjj,
@v_ping_reg_con,
@v_man,
@v_women,
@v_age20,
@v_age29,
@v_age39,
@v_age49,
@v_age50,
@v_citysh,
@v_citybj,
@v_cityhz,
@v_citygz,
@v_citysz,
@v_cityqt,
@v_ping_noreg,
@v_ping_noreg_ejj,
@v_ping_noreg_bjj,
@v_ping_noreg_con,
@v_ejj_user,
@v_bjj_user,
CONVERT(char(10), dateadd(dd,-1,getdate()),20))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
- proc 储过程
- proc调用存储过程
- proc存储过程及函数
- SAS 常用过程之 PROC UNIVARIATE
- 一个编写过程的例子 ArraySum PROC
- Proc Means：Means过程介绍
- Proc Freq：Freq过程介绍
- SAS常用过程之PROC UNIVARIATE
- SAS 中 PROC SQL过程步详解
- proc
- proc
- /proc
- proc
- proc
- proc
- /proc
- proc
- /proc/
- 儿时的游戏，你还记得多少？谨以此献给80年以前出生那一代人，80年以后出生的勿入！
- 以感恩的心态面对一切——微软一位副总裁的故事
- 将应用程序变成产品的过程中的十三条生存法则
- 读易[22]·说一说创业的精神
- “参加磊客中国公测，免费获赠《Web性能测试实战》作者签名本，更有机会加盟光芒国际 ”活动结果公布
- proc 储过程
- 测试术语和常见问题
- XForm + Ajax
- 论语(部分)转载
- 推荐一个正在发展的论坛－－测试论坛中的《读者》
- 李开复：21世纪7种人才最抢手
- 2008..CSDN之旅~
- 好习惯的力量
- 《LoadRunner从入门到精通》策划中——《Web性能测试实战》的姊妹篇调查意见
