34,594
社区成员
发帖
与我相关
我的任务
分享
select distinct Numbers,
事假=sum(case when EmployeesStatus =1 then num else 0 end),--统计事件这里没问题.我不知道旷工这里如何去统计.我下面的子查询没问题.这里不是熟.望各位解释一下.
旷工天数=
(
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber
)
from
(
select num=count(*),Numbers,EmployeesStatus,AbsenteeismHours from EmployeesAttendance
where DateMonths between (convert(varchar(8),'2011-05-01 00:00:00.000' ,120)+'01' ) and convert(char(10),dateadd(d,-1,convert(char(7),dateadd(m,1,'2011-05-01 00:00:00.000'),120)+ '-01 '),120)
group by Numbers,EmployeesStatus,AbsenteeismHours
) temp
group by Numbers
--我是要求这个语句的COUNT
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber
select distinct Numbers,
事假=sum(case when EmployeesStatus =1 then num else 0 end),--统计事件这里没问题.我不知道旷工这里如何去统计.我下面的子查询没问题.这里不是熟.望各位解释一下.
旷工天数=
(
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=SUM(cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2)))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber AND EmployeesAttendance .NUMBERS=TEMP.NUMBERS )
from
(
select num=count(*),Numbers,EmployeesStatus,AbsenteeismHours from EmployeesAttendance
where DateMonths between (convert(varchar(8),'2011-05-01 00:00:00.000' ,120)+'01' ) and convert(char(10),dateadd(d,-1,convert(char(7),dateadd(m,1,'2011-05-01 00:00:00.000'),120)+ '-01 '),120)
group by Numbers,EmployeesStatus,AbsenteeismHours
) temp
group by Numbers
Numbers 事假 旷工天数
-------------------------------------------------- ----------- ---------------------------------------
Msg 512, Level 16, State 1, Line 1
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
and EmployeesAttendance .numbers=temp.numbers
select distinct Numbers,
事假=sum(case when EmployeesStatus =1 then num else 0 end),--统计事件这里没问题.我不知道旷工这里如何去统计.我下面的子查询没问题.这里不是熟.望各位解释一下.
旷工天数=
(
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=SUM(cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2)))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber group by ClassNumber
)
from
(
select num=count(*),Numbers,EmployeesStatus,AbsenteeismHours from EmployeesAttendance
where DateMonths between (convert(varchar(8),'2011-05-01 00:00:00.000' ,120)+'01' ) and convert(char(10),dateadd(d,-1,convert(char(7),dateadd(m,1,'2011-05-01 00:00:00.000'),120)+ '-01 '),120)
group by Numbers,EmployeesStatus,AbsenteeismHours
) temp
group by Numbers
(
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber
)
(
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber adn xxx.numbers=temp.numbers
)
select distinct Numbers,
事假=sum(case when EmployeesStatus =1 then num else 0 end),--统计事件这里没问题.我不知道旷工这里如何去统计.我下面的子查询没问题.这里不是熟.望各位解释一下.
旷工天数=SUM(
(
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber
) )
from
(
select num=count(*),Numbers,EmployeesStatus,AbsenteeismHours from EmployeesAttendance
where DateMonths between (convert(varchar(8),'2011-05-01 00:00:00.000' ,120)+'01' ) and convert(char(10),dateadd(d,-1,convert(char(7),dateadd(m,1,'2011-05-01 00:00:00.000'),120)+ '-01 '),120)
group by Numbers,EmployeesStatus,AbsenteeismHours
) temp
group by Numbers