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提示:
Msg 512, Level 16, State 1, Line 1
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
事假=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
Msg 130, Level 15, State 1, Line 1
不能对包含聚合或子查询的表达式执行聚合函数。
--这个子查询是OK的.我怎么让这里可以统计.
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber
)
如果上述实例子查询结果是多条记录,就会出这样的错误提示,若加上一个与TEMP 表的连接条件不会出错。
(
--这个子查询是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
)
and xxx.numbers=temp.numbers
xxx.要为哪个表呢?
事假=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
group by ClassNumber
Msg 209, Level 16, State 1, Line 7
列名 'ClassNumber' 不明确。同时能告诉我哪出问题了吗?你是为什么想到这样的?
Numbers 事假 旷工天数
-------------------------------------------------- ----------- ---------------------------------------
Msg 512, Level 16, State 1, Line 1
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
事假=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统计,也不会出错,就是总的,你看起来不对,子查询里面统计,如果不分组不会出错,如果要分组,肯定就有多笔记录,当然就报错了,
我不是汇总,我是需要在一个下面temp临时表里面的number 旷工记录有多少条.
事假=sum(case when EmployeesStatus =1 then num else 0 end),--统计事件这里没问题.我不知道旷工这里如何去统计.我下面的子查询没问题.这里不是熟.望各位解释一下.
旷工天数=
(
--这个子查询是OK的.我怎么让这里可以统计.
select COUNT(1) 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
看来我的表达能力太差了...--我是要求这个语句的COUNT
select 员工旷工=cast(( EmployeesAttendance.AbsenteeismHours/ClassManage.fullTime) as decimal(5,2))
FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber
select COUNT(1) FROM EmployeesAttendance inner JOIN ClassManage ON EmployeesAttendance.ClassNumber=ClassManage.ClassNumber这就是COUNT,只不过放子查询后,要关联外面的NUMBERS,