存储过程学习

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Stat_User] (@StarDate DateTime)

AS
BEGIN
SET NOCOUNT ON;
DECLARE @StatDate DATETIME --统计日期
DECLARE @EndDate DATETIME --统计结束日期

--统计一个自然日
SET @StatDate= DATEDIFF(day, 1, @StarDate)
SET @EndDate = DATEADD(DAY, 1, @StatDate)

--删除临时表
if object_id('tempdb..#TUser') is not null Begin
drop table #TUser
End
if object_id('tempdb..#TLoginLog') is not null Begin
drop table #TLoginLog
End
--临时存储表变量
Create table #TUser--存储需要的用户信息
(
UserID INT,
UserLevelID INT,
ProvinceID INT,
RegisterDate DATETIME,
ValidMobileDate DATETIME,
RegisterSource INT,
ValidEmailDate DATETIME

)
DECLARE @TReview table(UserID INT,UserLevelID INT, ProvinceID INT )--存储用户评论信息
DECLARE @TConsultation table(UserID INT,UserLevelID INT, ProvinceID INT)--存储用户咨询信息
DECLARE @TVIPApply table(UserID INT,UserLevelID INT, ProvinceID INT)--存储用户申请信息

--用户表变量赋值
INSERT INTO #TUser
SELECT
UserID,
UserLevelID,
ProvinceID,
RegisterDate,
ValidMobileDate,
RegisterSource,
ValidEmailDate FROM dbo.TradeUser WITH (NOLOCK)
WHERE
(RegisterDate>=@STATDATE and RegisterDate<@EndDate)
or
(ValidMobileDate>=@STATDATE and ValidMobileDate<@EndDate)
or
(ValidEmailDate>=@STATDATE and ValidEmailDate<@EndDate)
and IsLocal<>1

--用户评论表变量赋值
INSERT INTO @TReview
SELECT
r.UserID,
t.UserLevelID,
t.ProvinceID
FROM dbo.Review as r WITH (NOLOCK) inner join dbo.TradeUser as t WITH (NOLOCK)
on r.UserID=t.UserID where ReviewdTime>=@STATDATE and ReviewdTime<@EndDate
and t.IsLocal<>1 and r.UserId<>0
--用户咨询表变量赋值
INSERT INTO @TConsultation
SELECT
r.UserID,
t.UserLevelID,
t.ProvinceID
FROM dbo.Consultation as r WITH (NOLOCK) inner join dbo.TradeUser as t WITH (NOLOCK)
on r.UserID=t.UserID where ConsultedTime>=@STATDATE and ConsultedTime<@EndDate
and t.IsLocal<>1 and r.UserId<>0
--用户申请表变量赋值
INSERT INTO @TVIPApply
SELECT
UserID,
0, --申请vip的都为普通用户
ProvinceID
FROM dbo.VIPApply WITH (NOLOCK)
where CreateDate>=@STATDATE and CreateDate<@EndDate
and UserID not in (select userId from tradeUser with(nolock) where IsLocal=1)
--相关联的省和用户级别
;with pu as
(
select ProvinceID,UserLevelID from #TUser Group by ProvinceID,UserLevelID
union
select ProvinceID,UserLevelID from @TReview Group by ProvinceID,UserLevelID
union
select ProvinceID,UserLevelID from @TConsultation Group by ProvinceID,UserLevelID
union
select ProvinceID,UserLevelID from @TVIPApply Group by ProvinceID,UserLevelID
),
--注册用户
Register as
(
select COUNT(*) as RegisterCount ,ProvinceID, UserLevelID FROM #TUser
WHERE RegisterDate>=@STATDATE AND RegisterDate<@EndDATE group by ProvinceID, UserLevelID
),
--==IOS版注册数
appIOS as
(SELECT COUNT(*) AS IOSCount,ProvinceID, UserLevelID FROM #TUser
WHERE RegisterDate>=@STATDATE AND RegisterDate<@EndDATE AND RegisterSource=2 group by ProvinceID, UserLevelID
),
--==安卓版注册数
AppAndroid as
(SELECT COUNT(*) AS AndroidCount,ProvinceID, UserLevelID FROM #TUser
WHERE RegisterDate>=@STATDATE AND RegisterDate<@EndDATE AND RegisterSource=3 group by ProvinceID, UserLevelID
),
--手机验证通过用户
ValidMobile as
(
select COUNT(*) as ValidMobileCount ,ProvinceID, UserLevelID FROM #TUser
WHERE ValidMobileDate>=@STATDATE AND ValidMobileDate<@EndDATE group by ProvinceID, UserLevelID
),
--email验证通过数
ValidEmail as
(
select COUNT(*) as ValidEmailCount ,ProvinceID, UserLevelID FROM #TUser
WHERE ValidEmailDate>=@STATDATE AND ValidEmailDate<@EndDATE group by ProvinceID, UserLevelID
),
--vip申请数
VIPApply as
(
select COUNT(*) as VipApplyCount ,ProvinceID, UserLevelID FROM @TVIPApply group by ProvinceID, UserLevelID
),

--用户咨询数
Consultation as
(
select COUNT(*) as ConsultationCount ,ProvinceID, UserLevelID FROM @TConsultation group by ProvinceID, UserLevelID
),

--用户评论数
Review as
(
select COUNT(*) as ReviewCount,ProvinceID, UserLevelID FROM @TReview as r group by ProvinceID, UserLevelID
)


--插入
INSERT INTO dbo.StatUser
(
StatDate,
RegisterCount,

IOSCount,
AndroidCount,

ValidMobileCount,
ValidEmailCount,
VipApplyCount,
ConsultationCount,
ReviewCount,
ProvinceID,
UserLevelID,
CreateDate
)
select @StatDate as StatDate,
r.RegisterCount,
ios.IOSCount,
aa.AndroidCount,
vm.ValidMobileCount,
ve.ValidEmailCount,
va.VIPApplyCount,
c.ConsultationCount,
rv.ReviewCount,
pu.*,
getdate() as CreateDate
from pu
left join Register as r on pu.UserLevelId = r.UserLevelId AND pu.ProvinceID = r.ProvinceID

left join appIOS as ios on pu.UserLevelId = ios.UserLevelId AND pu.ProvinceID = ios.ProvinceID
left join AppAndroid as aa on pu.UserLevelId = aa.UserLevelId AND pu.ProvinceID = aa.ProvinceID

left join ValidMobile as vm on pu.UserLevelId = vm.UserLevelId AND pu.ProvinceID = vm.ProvinceID
left join ValidEmail as ve on pu.UserLevelId = ve.UserLevelId AND pu.ProvinceID = ve.ProvinceID
left join VIPApply as va on pu.UserLevelId = va.UserLevelId AND pu.ProvinceID = va.ProvinceID
left join Consultation as c on pu.UserLevelId = c.UserLevelId AND pu.ProvinceID = c.ProvinceID
left join Review as rv on pu.UserLevelId = rv.UserLevelId AND pu.ProvinceID = rv.ProvinceID
END