时间戳

首页 > 技术文章 > 年龄段统计

年龄段统计

chenmfly 2018-05-04 23:06 原文


--以出生日期来统计
select nnd as '年龄段',count(*) as '人数' from
(
select
case
	when Birthday>= DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0) then '25岁以下'
	when Birthday BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-30, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-26, 0) then '26-30'
	when Birthday BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-35, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-30, 0) then '31-35'
	when Birthday BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-40, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-36, 0) then '36-40'
	when Birthday BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-45, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-40, 0) then '41-45'
	when Birthday BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-50, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-46, 0) then '46-50'
	else  '50岁以上'
end
as nnd from HR_Person WHERE IsLock=0)
person
group by nnd


--工龄

select nnd as '工作时间',count(*) as '人数' from
(
select
case
	when PoliceDate>= DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0) then '5年以下'
	when PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-4, 0) then '6-10'
	when PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-9, 0) then '11-15'
	when PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-14, 0) then '16-20'
	when PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-19, 0) then '21-25'	
	else  '26岁以上'
end
as nnd from HR_Person WHERE IsLock=0)
person
group by nnd

select nnd as '年龄段',count(*) as '人数',sex as '性别' from
(
select
case
when age>=1 and age<=10 then '1-10'
when age>=11 and age<=20 then '11-20'
when age>=21 and age<=30 then '21-30'
when age>=31 and age<=40 then '31-40' else 'other'
end
as nnd,uname,sex from #t
)
a
group by nnd,sex

ALTER PROCEDURE [dbo].[SumPoliceDate]
@strWhere VARCHAR(500)='',
@pwd VARCHAR(100)='admin123'
AS
BEGIN
  -- routine body goes here, e.g.
  -- SELECT 'Navicat for SQL Server'
DELETE FROM TempTable WHERE SumField='PoliceDate';

INSERT INTO TempTable  SELECT
Name='5年以下'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate>= DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)
,SortCode='1',Per='';  

INSERT INTO TempTable  SELECT
Name='6年-10年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)
,SortCode='2',Per=''; 

INSERT INTO TempTable  SELECT
Name='11年-15年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0)
,SortCode='3',Per=''; 

INSERT INTO TempTable  SELECT
Name='15年-20年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0)
,SortCode='4',Per='';

INSERT INTO TempTable  SELECT
Name='21年-25年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0)
,SortCode='5',Per='';

INSERT INTO TempTable  SELECT
Name='25年以上'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate< DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)
,SortCode='6',Per='';  

SELECT * FROM TempTable WHERE SumField='PoliceDate';
END
--


ALTER PROCEDURE [dbo].[SumPoliceDate]
@strWhere VARCHAR(500)='',
@pwd VARCHAR(100)='admin123'
AS
BEGIN
  -- routine body goes here, e.g.
  -- SELECT 'Navicat for SQL Server'
DELETE FROM TempTable WHERE SumField='PoliceDate';

INSERT INTO TempTable  SELECT
Name='5年以下'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate>= DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)
,SortCode='1',Per='';  

INSERT INTO TempTable  SELECT
Name='6年-10年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-5, 0)
,SortCode='2',Per=''; 

INSERT INTO TempTable  SELECT
Name='11年-15年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-10, 0)
,SortCode='3',Per=''; 

INSERT INTO TempTable  SELECT
Name='15年-20年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-15, 0)
,SortCode='4',Per='';

INSERT INTO TempTable  SELECT
Name='21年-25年'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0) AND DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-20, 0)
,SortCode='5',Per='';

INSERT INTO TempTable  SELECT
Name='25年以上'
,SumField='PoliceDate'
,ShuLiang = (SELECT Count(1) FROM [dbo].[HR_Person] WHERE IsLock=0 AND PoliceDate< DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)+@strWhere) 
,Birthday1=DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)
,Birthday2=DATEADD(yy, DATEDIFF(yy,0,getdate())-25, 0)
,SortCode='6',Per='';  

SELECT * FROM TempTable WHERE SumField='PoliceDate';
END

推荐阅读