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
解决方案 »
- 求一简单SQL
- 跪求,泣求,高手救命,一SQL语句的优化,特急,解决就结贴
- SQL问题。高手帮帮忙。
- 100分求助distinct + group by 在线等
- 哪里有sql server 2000桌面版的英文补丁下载.
- 诚求:共同合作编写小型POS收款系统 小型人事工资系统等软件
- 如何使用T-sql得到一个数据库上存储的表的信息
- 急需实现数据更新,100大分寻求SQL语句!
- sql server2000中,由触发器生成的值怎么取?我用rs.addnew rs.update插入后,不能用rs("XX")取得值?
- 急问呢,两个用户同时对一条记录操作怎么办?
- 在64位windows2012安装SQL express 2014,提取文件失败
- @V1 in ('',ID001)的优化方法
drop table tableA
gocreate 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
gocreate table tableB (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableBSELECT '李四', '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次
drop table tableA
gocreate 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
gocreate table tableB (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableBSELECT '李四', '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次
赵五下井 超过十个小时 应该计算下井次数为2啊 !
drop table tableA
gocreate 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
gocreate table tableB (name VARCHAR(8),rjsj DATETIME,cjsj DATETIME)
insert tableBSELECT '李四', '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次
赵五下井 超过十个小时 应该计算下井次数为2啊 !查询条件中的datediff(day,a.rjsj,b.rjsj)=0改下就行了