count(*) 计算下井次数,该怎么解决
日期：2014-05-17 浏览次数：20633 次
count(*) 计算下井次数
select distinct e.name,d.name,count(e.name)as 下井次数
from attendance a ,employee e,department d
where a.cardNum=e.cardnumber and d.id=e.departmentId
and a.occDay between 20131213 and 20131222 and
datediff(hour,a.inTime,a.outTime)>2
group by e.name,d.name
码农我根据e.name出现的次数 来统计下井次数。
可是现在业务出现新情况 datediff(hour,a.inTime,a.outTime)>2 算 一次下井
2<datediff(hour,a.inTime,a.outTime)<12算 1.5次
datediff(hour,a.inTime,a.outTime)>12算 2次 下井
那么此sql怎么改动呢？
------解决方案--------------------
select distinct e.name,d.name,count(e.name)as 下井次数
from attendance a ,employee e,department d
where a.cardNum=e.cardnumber and d.id=e.departmentId
and a.occDay between 20131213 and 20131222 and
datediff(hour,a.inTime,a.outTime)>2
group by e.name,d.name
码农我根据e.name出现的次数 来统计下井次数。
可是现在业务出现新情况 datediff(hour,a.inTime,a.outTime)>2 算 一次下井
2<datediff(hour,a.inTime,a.outTime)<12算 1.5次
datediff(hour,a.inTime,a.outTime)>12算 2次 下井
那么此sql怎么改动呢？
------解决方案--------------------
select distinct e.name,d.name,
sum(case when datediff(hour,a.inTime,a.outTime)>2 then 1
when 2<datediff(hour,a.inTime,a.outTime)<12 then 1.5
when datediff(hour,a.inTime,a.outTime)>12 then 2
else 0
end)as 下井次数
from attendance a ,employee e,department d
where a.cardNum=e.cardnumber and d.id=e.departmentId
and a.occDay between 20131213 and 20131222
--and datediff(hour,a.inTime,a.outTime)>2
group by e.name,d.name
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
