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