34,576
社区成员
发帖
与我相关
我的任务
分享
select 员工资料表.学历代码, count(员工ID) as '人数'
from 员工资料表,部门表
where 员工资料表.部门ID =部门表.部门ID and 员工资料表.在职状态 = 在职 and
部门表.部门内码 like '(这里是动态传参,点击导航节点)%'
group by 员工资料表.学历代码
select 员工资料表.学历代码, count(员工ID) as '人数' ,
datename(yy,员工资料表.离职时间)+datename(mm,员工资料表.离职时间) as '年月'
from 员工资料表,部门表
where 员工资料表.部门ID =部门表.部门ID and 员工资料表.在职状态 = 在职 and
部门表.部门内码 like '(这里是动态传参,点击导航节点)%'
group by 员工资料表.学历代码,
datename(yy,员工资料表.离职时间)+datename(mm,员工资料表.离职时间)
order by '年月'
员工资料表.离职时间 between 年份.1.1-年份1.31
create proc get_yymonth(@year varchar(4))
as
;with cte as
(
select @year + '-' + right(100 + number,2) as ym
from master..spt_values
where [type] = 'p' and number between 1 and 12
)
select *
from cte
/**
*可以加你的查询语句
*
*select *
*from cte a left join tb b on a.ym = convert(varchar(7),[日期],120)
*where ...
*/
go
exec get_yymonth '2011'
drop proc get_yymonth
/*
ym
---------
2011-01
2011-02
2011-03
2011-04
2011-05
2011-06
2011-07
2011-08
2011-09
2011-10
2011-11
2011-12
(12 行受影响)
*/
-- 方法很多
select
a.学历代码,
sum(CASE WHEN MONTH(a.Date)=1 THEN 1 ELSE 0 END) AS [1月],
sum(CASE WHEN MONTH(a.Date)=2 THEN 1 ELSE 0 END) AS [2月],
sum(CASE WHEN MONTH(a.Date)=3 THEN 1 ELSE 0 END) AS [3月],
sum(CASE WHEN MONTH(a.Date)=4 THEN 1 ELSE 0 END) AS [4月],
sum(CASE WHEN MONTH(a.Date)=5 THEN 1 ELSE 0 END) AS [5月],
sum(CASE WHEN MONTH(a.Date)=6 THEN 1 ELSE 0 END) AS [6月],
sum(CASE WHEN MONTH(a.Date)=7 THEN 1 ELSE 0 END) AS [7月],
.......................
,count(a.员工ID) as '總人数'
from 员工资料表 AS a ,部门表 AS b
where a.部门ID =b.部门ID and a.在职状态 = 在职 and
b.部门内码 like '(这里是动态传参,点击导航节点)%'
AND year(a.Date)=2010 --條件為2010年
group by a.学历代码