34,576
社区成员
发帖
与我相关
我的任务
分享
掉了个时间条件。
select
sum(case when datediff(n,b.rjsj,b.cjsj)>120 then 1
when datediff(n,b.rjsj,b.cjsj)>480 and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
when datediff(n,b.rjsj,b.cjsj)>600 then 2
else 0
end)as 下井次数
from tableA a left join tableB b
on a.name=b.name and datediff(day,a.rjsj,b.rjsj)=0
select
sum(case when datediff(n,b.rjsj,b.cjsj)>120 then 1
when datediff(n,b.rjsj,b.cjsj)>480 and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
when datediff(n,b.rjsj,b.cjsj)>600 then 2
else 0
end)as 下井次数
from tableA a left join tableB b
on a.name=b.name
算出12次。。。。应该是3。。。
select
sum(case when datediff(hour,b.rjsj,b.cjsj)>2 then 1
when 8<datediff(hour,b.rjsj,b.cjsj)<10 then 1.5
when datediff(hour,b.rjsj,b.cjsj)>10 then 2
else 0
end)as 下井次数
from tableA a left join tableB b
where a.name=b.name