年龄段统计

博客园 - 雪夜

VS统计代码量 DataTable 操作 - 雪夜 - 博客园统计销售额 bootstrap 知识点人脸识别参考转盒子 model 数据注解 delphi ScriptGate 调用JS C#区块链零基础入门，学习路线图转 SQLServer 2005 和自增长主键identity说再见——NEWSEQUENTIALID() ASP.NET Core and .NET Core Library Support 创新县（市、区）的主要条件 C# imgage图片转base64字符/base64字符串转图片另存成 base64编码的文件图片 CSS - 雪夜 - 博客园 Exception has been thrown by the target of an invocation 网站报错高效通用分页存储过程多表查询 JavaScript 知识记录 JQuery 全选取消博文阅读密码验证 - 博客园

年龄段统计

雪夜 · 2018-05-04 · via 博客园 - 雪夜


--以出生日期来统计
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

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 雪夜