34,576
社区成员
发帖
与我相关
我的任务
分享
name rjsj(入井时间) cjsj(出井时间)
李四 2014-01-01 08:30:00 2014-01-01 15:20:20
李四 2014-01-02 09:01:01 2014-01-02 11:40:51
李四 2014-01-03 10:01:57 2014-01-03 15:00:01
赵五 2014-01-01 09:50 2014-01-01 23:00:00
陈六
冯八
name rjsj(入井时间) cjsj(出井时间)
李四 2014-01-01 08:41:01 2014-01-01 15:01:00
李四 2014-01-02 09:15:01 2014-01-02 11:20:20
李四 2014-01-03 10:11:50 2014-01-03 14:50:01
李四 2014-01-04 14:20:00 2014-01-04 17:01:07
赵五 2014-01-01 10:01:01 2014-01-01 23:05:01
陈六
冯七
李四 4
赵五 1
陈六 0
冯七 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 and datediff(day,a.rjsj,b.rjsj)=0
if object_id('tableA') is not null
drop table tableA
go
create table tableA (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableA
SELECT '李四', '2014-01-01 08:30:00', '2014-01-01 15:20:20' UNION ALL
SELECT '李四', '2014-01-02 09:01:01', '2014-01-02 11:40:51' UNION ALL
SELECT '李四', '2014-01-03 10:01:57', '2014-01-03 15:00:01' UNION ALL
SELECT '赵五', '2014-01-01 09:50:00' ,'2014-01-01 23:00:00' UNION ALL
SELECT '陈六','','' UNION ALL
SELECT '冯八','',''
if object_id('tableB') is not null
drop table tableB
go
create table tableB (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableB
SELECT '李四', '2014-01-01 08:41:01','2014-01-01 15:01:00'UNION ALL
SELECT '李四', '2014-01-02 09:15:01', '2014-01-02 11:20:20'UNION ALL
SELECT '李四', '2014-01-03 10:11:50', '2014-01-03 14:50:01'UNION ALL
SELECT '李四', '2014-01-04 14:20:00', '2014-01-04 17:01:07'UNION ALL
SELECT '赵五', '2014-01-01 10:01:01', '2014-01-01 23:05:01'UNION ALL
SELECT '陈六','',''UNION ALL
SELECT '冯七','',''
go
select a.name,
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
GROUP BY a.name
----------------
name 下井次数
陈六 0.0
冯八 0.0
李四 3.0
赵五 1.0
----------------
李四按照你的要求应该是下井次数为3次[/quote]
赵五下井 超过十个小时 应该计算下井次数为2啊 ![/quote]查询条件中的datediff(day,a.rjsj,b.rjsj)=0改下就行了
select
name,
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
group by name
if object_id('tableA') is not null
drop table tableA
go
create table tableA (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableA
SELECT '李四', '2014-01-01 08:30:00', '2014-01-01 15:20:20' UNION ALL
SELECT '李四', '2014-01-02 09:01:01', '2014-01-02 11:40:51' UNION ALL
SELECT '李四', '2014-01-03 10:01:57', '2014-01-03 15:00:01' UNION ALL
SELECT '赵五', '2014-01-01 09:50:00' ,'2014-01-01 23:00:00' UNION ALL
SELECT '陈六','','' UNION ALL
SELECT '冯八','',''
if object_id('tableB') is not null
drop table tableB
go
create table tableB (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableB
SELECT '李四', '2014-01-01 08:41:01','2014-01-01 15:01:00'UNION ALL
SELECT '李四', '2014-01-02 09:15:01', '2014-01-02 11:20:20'UNION ALL
SELECT '李四', '2014-01-03 10:11:50', '2014-01-03 14:50:01'UNION ALL
SELECT '李四', '2014-01-04 14:20:00', '2014-01-04 17:01:07'UNION ALL
SELECT '赵五', '2014-01-01 10:01:01', '2014-01-01 23:05:01'UNION ALL
SELECT '陈六','',''UNION ALL
SELECT '冯七','',''
go
select a.name,
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
GROUP BY a.name
----------------
name 下井次数
陈六 0.0
冯八 0.0
李四 3.0
赵五 1.0
----------------
李四按照你的要求应该是下井次数为3次[/quote]
赵五下井 超过十个小时 应该计算下井次数为2啊 !if object_id('tableA') is not null
drop table tableA
go
create table tableA (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableA
SELECT '李四', '2014-01-01 08:30:00', '2014-01-01 15:20:20' UNION ALL
SELECT '李四', '2014-01-02 09:01:01', '2014-01-02 11:40:51' UNION ALL
SELECT '李四', '2014-01-03 10:01:57', '2014-01-03 15:00:01' UNION ALL
SELECT '赵五', '2014-01-01 09:50:00' ,'2014-01-01 23:00:00' UNION ALL
SELECT '陈六','','' UNION ALL
SELECT '冯八','',''
if object_id('tableB') is not null
drop table tableB
go
create table tableB (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableB
SELECT '李四', '2014-01-01 08:41:01','2014-01-01 15:01:00'UNION ALL
SELECT '李四', '2014-01-02 09:15:01', '2014-01-02 11:20:20'UNION ALL
SELECT '李四', '2014-01-03 10:11:50', '2014-01-03 14:50:01'UNION ALL
SELECT '李四', '2014-01-04 14:20:00', '2014-01-04 17:01:07'UNION ALL
SELECT '赵五', '2014-01-01 10:01:01', '2014-01-01 23:05:01'UNION ALL
SELECT '陈六','',''UNION ALL
SELECT '冯七','',''
go
select a.name,
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
GROUP BY a.name
----------------
name 下井次数
陈六 0.0
冯八 0.0
李四 3.0
赵五 1.0
----------------
李四按照你的要求应该是下井次数为3次