sql统计查询、仅供参考(一)
/***人数统计Sql**/ /** 查询行政部***/
Declare @DepartID int;Declare @Alltotal int; Select @DepartID=Count(*) From Emp_Base where DepartID=2 select @Alltotal=Count(*) from emp_base SELECT SUM(@DepartID) AS DepartIDCount, SUM(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@DepartID)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*400/@Alltotal as 长度 FROM Emp_Base where DepartID=2 Group By DepartID
Declare @DepartID int;Declare @Alltotal int; Select @DepartID=Count(*) From Emp_Base where DepartID=2 select @Alltotal=Count(*) from emp_base SELECT SUM(@DepartID) AS DepartIDCount, SUM(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@DepartID)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*400/@Alltotal as 长度 FROM Emp_Base where DepartID=2 Group By DepartID
/***查询男女比例**/
Declare @Sex int;Declare @Alltotal int; Select @Sex=Count(*) From Emp_Base where Sex=1 select @Alltotal=Count(*) from emp_base SELECT SUM(@Sex) AS sexCount, SUM(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@Sex))*100/ CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比, Count(*)*50/@Alltotal as 长度 FROM Emp_Base where Sex=1 Group By Sex select Count(*) sex from Emp_Base where sex=1
/**查询部门为2的总人数和百分比***/
Declare @DepartID int;Declare @Alltotal int; Select @DepartID=Count(*) From Emp_Base where DepartID=2 select @Alltotal=Count(*) from emp_base SELECT count(@DepartID) AS DepartIDCount, count(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@DepartID)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*100/@Alltotal as 长度 FROM Emp_Base where DepartID=2
/**登陆次数百分比***/
Declare @total int;Declare @t table(myDay varchar(50),display varchar(50),DayID tinyint);Select @total=Count(*) From U_LoginLog; Insert into @t Select '1,2,3,4','01-04',1; Insert into @t Select '5,6,7,8','05-08',2; Insert into @t Select '9,10,11,12','09-12',3; Insert into @t Select '13,14,15,16','13-16',4; Insert into @t Select '17,18,19,20','17-20',5; Insert into @t Select '21,22,23,24,25','21-25',6; Insert into @t Select '26,27,28,29,30,31','26-31',7; Select display,SUM(次数)*100/@total as 次数,SUM(次数)*400/@total as 长度 From(Select LoginTime, Count(*) as 次数 From (Select DATENAME(day,LoginTime) as LoginTime From U_LoginLog
/**职位百分比*/
Declare @CorpposID int ;Declare @Alltotal int; Select @CorpposID=Count(*) From Emp_Base where CorpposID=1 select @Alltotal=Count(*) from emp_base SELECT SUM(@CorpposID) AS DepartIDCount, SUM(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@CorpposID)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*10/@Alltotal as 长度 FROM Emp_Base left join U_Corppos on U_Corppos.CorpposID = Emp_Base.CorpposID
Declare @DepartID int;Declare @Alltotal int; Select @DepartID=Count(*) From Emp_Base where DepartID=2 select @Alltotal=Count(*) from emp_base SELECT count(@DepartID) AS DepartIDCount, count(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@DepartID)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*100/@Alltotal as 长度 FROM Emp_Base where DepartID=2
Declare @CorpposID int ;Declare @Alltotal int; select @CorpposID=Count(*) From Emp_Base where Emp_Base.CorpposID=3 select @Alltotal=Count(*) from emp_base select a.CorpposID, SUM(@CorpposID) AS DepartIDCount, SUM(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@CorpposID)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*10/@Alltotal as 长度 FROM Emp_Base a Left Join U_Corppos b on a.CorpposID=b.CorpposID where a.CorpposID=1 group by a.CorpposID
select a.CorpposID,b.CorpposID,b.Corppos from Emp_Base a ,U_Corppos b where a.CorpposID=b.CorpposID SELECT a.*,b.CorpposID,b.Corppos FROM Emp_Base a Left Join U_Corppos b on a.CorpposID=b.CorpposID
/**职位数量、百分比**/ d
eclare @count float select @count=count(1) from emp_base select corpposid,count(corpposid) as 职位数量 ,(convert(varchar(20),(count(corpposid)/@count)*100)+'%') as 百分比 from emp_base a group by corpposId
/**职位名称**/ select Distinct(b.corppos), a.corpposId, b.corpposId from emp_base a left join U_Corppos b on a.corpposId=b.corpposId /**各职位数据*/ select count(corpposid) as 职位数量 from emp_base group by corpposId select Distinct Corppos from U_Corppos select count(1) from emp_base /***职位人数统计**/ declare @count float select @count=count(1) from emp_base select a.corpposid,max(b.corppos)corppos, count(a.corpposid) as 职位数量 ,(convert(varchar(20),(count(a.corpposid)/@count)*100)+'%') as 百分比 from emp_base a left join U_Corppos b on a.corpposId=b.corpposId group by a.corpposId
/**部门人数统计**/
declare @count float select @count=count(1) from emp_base select a.DepartID,max(b.Depart)Depart, count(a.DepartID) as 职位数量 ,(convert(varchar(20),(count(a.DepartID)/@count)*100)+'%') as 百分比 from emp_base a left join U_Depart b on a.DepartID=b.DepartID group by a.DepartID
/**性别统计**/
declare @count float select @count=count(1) from emp_base select case when sex=1 then '男' when sex=0 then '女' end as sex1,count(sex) as 性别数量 ,(convert(varchar(20),(count(sex)/@count)*100)+'%') as 百分比 from emp_base a group by sex
/**地区分配统计***/
declare @count float select @count=count(1) from emp_base select a.ProvinceID,max(b.Province)Province, count(a.ProvinceID) as 地区数量 ,(convert(varchar(20),(count(a.ProvinceID)/@count)*100)+'%') as 百分比 from emp_base a left join U_Province b on a.ProvinceID=b.ProvinceID group by a.ProvinceID
/**年龄统计**/
declare @count float select @count=count(1) from emp_base select a.BirthDate,max(a.BirthDate)BirthDate, count(BirthDate) as 年龄数量 ,(convert(varchar(20),(count(a.BirthDate)/@count)*100)+'%') as 百分比 from emp_base a group by BirthDate
Declare @old int;Declare @Alltotal int; Select @old=count(*) from emp_base where datediff(year,emp_base.BirthDate,getdate()) >=60 and datediff(year,emp_base.BirthDate,getdate())<=100 select @Alltotal=Count(*) from emp_base SELECT count(@old) AS oldcount, count(@Alltotal) AS Alltotal, CAST(CONVERT (float, SUM(@old)) * 100 / CONVERT (float, SUM(@Alltotal)) AS decimal(38 , 2)) AS 百分比,Count(*)*400/@Alltotal as 长度 FROM Emp_Base where datediff(year,emp_base.BirthDate,getdate()) >=60 and datediff(year,emp_base.BirthDate,getdate())<=100 Group By BirthDate
select BirthDate,count(*) as 各年龄人数 from emp_base group by BirthDate select COUNT(*) as 年龄人数,BirthDate from emp_base where BirthDate between (year(getdate())-year(BirthDate)=18) and (year(getdate())-year(BirthDate)=25)
SELECT BirthDate FROM emp_base WHERE BirthDate BETWEEN (year(getdate())-year(BirthDate)) AND (year(getdate())-year(BirthDate))
select BirthDate,datediff(year,BirthDate,getdate()) as '年龄' from emp_base
select BirthDate,datediff("yyyy",emp_base.BirthDate,getdate()) as 年龄 from emp_base
select count(*) from emp_base where datediff("yyyy",emp_base.BirthDate,getdate()) >=18 and datediff("yyyy",emp_base.BirthDate,getdate())<=25
/**当月生日情况，员工姓名统计**/
select * from emp_base where datediff(month,BirthDate,getdate())=0 ---本月
/**本周过生日情况，员工姓名统计**/
SELECT Name,(dateadd(year,datediff(year,BirthDate,getdate()),BirthDate)) AS Nbirthday FROM emp_base WHERE (dateadd(year,datediff(year,BirthDate,getdate()),BirthDate)) BETWEEN getdate() AND getdate()+7
