sql统计查询、仅供参考(二)
要想做好统计数据查询你首先要连接以下几个函数的意思，在进行下一步
函数：有关计算的函数
Data 教程连接 http://www.w3school.com.cn/sql/sql_dates.asp
DATEADD 教程连接 http://www.w3school.com.cn/sql/func_dateadd.asp
DATEDIFF 教程连接 http://www.w3school.com.cn/sql/func_datediff.asp
Convert 教程连接 http://www.w3school.com.cn/sql/func_convert.asp
DATEPART 教程连接 http://www.w3school.com.cn/sql/func_datepart.asp
Sum 教程连接 http://www.w3school.com.cn/sql/sql_func_sum.asp
/****入职率***/
declare @IsShow int; declare @AllIsShow int;
select @IsShow=count(*) from Emp_Base where IsShow=1 and datepart(mm,JoinTime) =datepart(mm,getdate()) select @AllIsShow=count(*) from Emp_Base
SELECT count(@IsShow) AS DepartIDCount, count(@AllIsShow) AS Alltotal, CAST(CONVERT (float, SUM(@IsShow)) * 100 / CONVERT (float, SUM(@AllIsShow)) AS decimal(38 , 2)) AS 百分比 FROM Emp_Base
decimal(2,1)，有效长度为2，小数位占1位。
/***当月入职人数**/
select (Count(*)) as 入职人数 from Emp_Base where IsShow=1 and datepart(mm,JoinTime) =datepart(mm,getdate())
select datepart(dd,JoinTime) from emp_base
/***人数统计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=12 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=12
select * from Emp_Base where DepartID=2
select COUNT(*) from Emp_Base
select * from U_Depart 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
/**职位数量、百分比**/
declare @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 职位数量 ,(str(convert(varchar(20),(count(a.corpposid)/@count)*100),5,2)+'%') as 百分比 from emp_base a
left join U_Corppos b on a.corpposId=b.corpposId group by a.corpposId
select Salary from emp_base where group by corpposId
/**部门人数统计**/
declare @count float select @count=count(1) from emp_base
select a.DepartID,max(b.Depart)Depart, count(a.DepartID) as 职位数量 ,(str((convert(varchar(20),(count(a.DepartID)/@count)*100)),5,2)+'%') as 百分比 from emp_base a
left join U_Depart b on a.DepartID=b.DepartID group by a.DepartID
select str((convert(float,(@czcj/@ckrs)*100)),5,2)+'%'或者select convert(varchar,convert(decimal(10,2),(@czcj/@ckrs)*100))+'%'
/**性别统计**/
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 性别数量 ,(str(convert(varchar(20),(count(sex)/@count)*100),5,2)+'%') 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 地区数量 ,(str(convert(varchar(20),(count(a.ProvinceID)/@count)*100),5,2)+'%') 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()) >=41 and datediff(year,emp_base.BirthDate,getdate())<=50 select @Alltotal=Count(*)
from emp_base
SELECT DISTINCT(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()) >=41 and datediff(year,emp_base.BirthDate,getdate())<=50
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 count(*) from emp_base where datediff(year,emp_base.BirthDate,getdate()) >=41 and datediff(year,emp_base.BirthDate,getdate())<=50
/**当月生日情况，员工姓名统计**/
select Name from emp_base where datediff(month,BirthDate,getdate())=0 ---本月
/**最近7天过生日情况，员工姓名统计**/
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()+30
/**最近30天过生日的员工统计**/
select name from emp_base where (dateadd(year,datediff(year,BirthDate,getdate()),BirthDate)) BETWEEN getdate() AND getdate()+30
/**每月过生日的员工统计**/
select Name,convert(varchar(10),(dateadd(year,datediff(year,BirthDate,getdate()),BirthDate)),120) AS Nbirthday from emp_base
where datediff(month,dateadd(yy,datediff(yy,birthdate,getdate()),birthdate),getdate())=0 ---每月
/**总薪资*/
select convert(int,sum(Salary)) 总工资 from emp_base
/*年龄**/
Declare @old int;Declare @Alltotal int; Select @old=count(*) from emp_base where datediff(year,emp_base.BirthDate,getdate()) >=81
and datediff(year,emp_base.BirthDate,getdate())<=90 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()) >=81 and datediff(year,emp_base.BirthDate,getdate())<=90 Group By BirthDate
/*xiaoZ(2509500286) 16:59:52
我也没你字段
select 职位，职位人数,convert(float,count(count(员工id)))/(select count(员工id) from 员工表)
as 占比
from 员工表 inner jion 职位信息表 on 人员.职位id=职位信息表.职位id
group by 职位信息表.职位id
职位人数就是count(员工id)
as 后面是占比
我没写上去*/
