存储过程统计年月日的访问人数

原创

zhangyongshizhi 2022-08-06 00:28:47 博主文章分类：数据库SQL Server ©著作权

©著作权归作者所有：来自51CTO博客作者zhangyongshizhi的原创作品，请联系作者获取转载授权，否则将追究法律责任

为了统计每天、每月的体侧人数，利用sql函数写成了存储过程，如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [dbo].[usp_GetStatisticsValue]
ALTER PROC [dbo].[usp_GetStatisticsValue]
AS
BEGIN

--定义一张临时变量表
DECLARE @tbl TABLE(year int,month int,day int)

DECLARE @total int
DECLARE @year int
DECLARE @month int
DECLARE @day int

--tbl_physical_fitness 体侧项目表  获得总的体健数
SELECT @total = COUNT(1) FROM [dbo].[tbl_physical_fitness]

INSERT INTO @tbl
SELECT DATEPART(YEAR,exam_date),DATEPART(MONTH,exam_date),DATEPART(DAY,exam_date) FROM tbl_exam WHERE DATEPART(YEAR,exam_date) = '2015' -- DATEPART(YEAR,GETDATE())
SELECT @year = @@ROWCOUNT

SELECT @month = COUNT(1) FROM @tbl WHERE month = DATEPART(MONTH,GETDATE())
SELECT @day = COUNT(1) FROM @tbl WHERE day = DATEPART(DAY,DATEADD(DAY,-1,GETDATE())) AND  month = DATEPART(MONTH,GETDATE())

SELECT @day AS pre_day,@month month,@year AS year,@total AS total
END