求一时间比较的SQL 指令

表数据
COL1                      COL2      COL2      COL4                        COL5
----------------------------------------------------------------------------------------------
2010-05-05 00:00:00.000 1000011 张红  1900-01-01 07:22:57.000 1900-01-01 19:31:46.000
2010-05-06 00:00:00.000 1000011 张红 1900-01-01 07:21:17.000 1900-01-01 19:30:30.000
2010-05-07 00:00:00.000 1000011 张红  1900-01-01 07:22:50.000 1900-01-01 19:30:46.000
2010-05-08 00:00:00.000 1000011 张红 1900-01-01 07:23:17.000 1900-01-01 19:30:50.000
2010-05-05 00:00:00.000 1061392 刘辉 1900-01-01 07:26:59.000 1900-01-01 17:33:52.000
2010-05-06 00:00:00.000 1061487 刘辉 1900-01-01 07:20:05.000 1900-01-01 16:32:26.000
2010-05-07 00:00:00.000 1061392 刘辉 1900-01-01 07:26:59.000 1900-01-01 17:30:52.000
2010-05-08 00:00:00.000 1061487 刘辉 1900-01-01 07:20:05.000 1900-01-01 17:32:26.000需求说明
表中有日期，工号，上班第一次考勤与最后一次考勤，同一天只有一条考勤记录，求两天之前的打卡时间差的小时数
（同一工号，前一天的最后一次考勤同次日的第一次考勤时间差）。

解决方案 »

  1.   

    用diff函数 convert转成字符串型
      

  2.   

    http://topic.csdn.net/u/20100519/06/1a026276-9b20-43ac-91fd-1f5a18b9cb74.html?55580
    参考这贴
      

  3.   

    create table tb(COL1 datetime , COL2 varchar(10), COL3 varchar(10),COL4 datetime,COL5 datetime)
    insert into tb values('2010-05-05 00:00:00.000', '1000011', '张红', '1900-01-01 07:22:57.000', '1900-01-01 19:31:46.000')
    insert into tb values('2010-05-06 00:00:00.000', '1000011', '张红', '1900-01-01 07:21:17.000', '1900-01-01 19:30:30.000')
    insert into tb values('2010-05-07 00:00:00.000', '1000011', '张红', '1900-01-01 07:22:50.000', '1900-01-01 19:30:46.000')
    insert into tb values('2010-05-08 00:00:00.000', '1000011', '张红', '1900-01-01 07:23:17.000', '1900-01-01 19:30:50.000')
    insert into tb values('2010-05-05 00:00:00.000', '1061392', '刘辉', '1900-01-01 07:26:59.000', '1900-01-01 17:33:52.000')
    insert into tb values('2010-05-06 00:00:00.000', '1061487', '刘辉', '1900-01-01 07:20:05.000', '1900-01-01 16:32:26.000')
    insert into tb values('2010-05-07 00:00:00.000', '1061392', '刘辉', '1900-01-01 07:26:59.000', '1900-01-01 17:30:52.000')
    insert into tb values('2010-05-08 00:00:00.000', '1061487', '刘辉', '1900-01-01 07:20:05.000', '1900-01-01 17:32:26.000')
    goselect m.* , isnull(datediff(hh,convert(varchar(10),n.col1,120) + ' ' + convert(varchar(8),n.col5,108), convert(varchar(10),m.col1,120) + ' ' + convert(varchar(8),m.col4,108)),0) [前一天的最后一次考勤同次日的第一次考勤时间差] 
    from tb m left join tb n 
    on m.COL3 = n.COL3 and datediff(dd,n.COL1,m.COL1) = 1drop table tb/*
    COL1                                                   COL2       COL3       COL4                                                   COL5                                                   前一天的最后一次考勤同次日的第一次考勤时间差 
    ------------------------------------------------------ ---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ---------------------- 
    2010-05-05 00:00:00.000                                1000011    张红         1900-01-01 07:22:57.000                                1900-01-01 19:31:46.000                                0
    2010-05-06 00:00:00.000                                1000011    张红         1900-01-01 07:21:17.000                                1900-01-01 19:30:30.000                                12
    2010-05-07 00:00:00.000                                1000011    张红         1900-01-01 07:22:50.000                                1900-01-01 19:30:46.000                                12
    2010-05-08 00:00:00.000                                1000011    张红         1900-01-01 07:23:17.000                                1900-01-01 19:30:50.000                                12
    2010-05-05 00:00:00.000                                1061392    刘辉         1900-01-01 07:26:59.000                                1900-01-01 17:33:52.000                                0
    2010-05-06 00:00:00.000                                1061487    刘辉         1900-01-01 07:20:05.000                                1900-01-01 16:32:26.000                                14
    2010-05-07 00:00:00.000                                1061392    刘辉         1900-01-01 07:26:59.000                                1900-01-01 17:30:52.000                                15
    2010-05-08 00:00:00.000                                1061487    刘辉         1900-01-01 07:20:05.000                                1900-01-01 17:32:26.000                                14（所影响的行数为 8 行）*/
      

  4.   

    表数据
    COL1 COL2 COL2 COL4 COL5
    ----------------------------------------------------------------------------------------------
    2010-05-05 00:00:00.000 1000011 张红 1900-01-01 07:22:57.000 1900-01-01 19:31:46.000
    2010-05-06 00:00:00.000 1000011 张红 1900-01-01 07:21:17.000 1900-01-01 19:30:30.000
    2010-05-07 00:00:00.000 1000011 张红 1900-01-01 07:22:50.000 1900-01-01 19:30:46.000
    2010-05-08 00:00:00.000 1000011 张红 1900-01-01 07:23:17.000 1900-01-01 19:30:50.000
    2010-05-05 00:00:00.000 1061392 刘辉 1900-01-01 07:26:59.000 1900-01-01 17:33:52.000
    2010-05-06 00:00:00.000 1061487 刘辉 1900-01-01 07:20:05.000 1900-01-01 16:32:26.000
    2010-05-07 00:00:00.000 1061392 刘辉 1900-01-01 07:26:59.000 1900-01-01 17:30:52.000
    2010-05-08 00:00:00.000 1061487 刘辉 1900-01-01 07:20:05.000 1900-01-01 17:32:26.000
    go
    create table checkwork
    (
    COL1 datetime,
    COL2 varchar(10),
    COL3 varchar(20),
    COL4 datetime,
    COL5 datetime,
    )
    go
    insert into checkwork(COL1,COL2,COL3,COL4,COL5)
    (
    select '2010-05-05 00:00:00.000','1000011','张红','2010-05-05 07:22:57.000','2010-05-05 19:31:46.000' union
    select '2010-05-06 00:00:00.000','1000011','张红','2010-05-06 07:21:17.000','2010-05-06 19:30:30.000' union
    select '2010-05-07 00:00:00.000','1000011','张红','2010-05-07 07:22:50.000','2010-05-07 19:30:46.000' union
    select '2010-05-08 00:00:00.000','1000011','张红','2010-05-08 07:23:17.000','2010-05-08 19:30:50.000' union
    select '2010-05-05 00:00:00.000','1061392','刘辉','2010-05-05 07:26:59.000','2010-05-05 17:33:52.000' union
    select '2010-05-06 00:00:00.000','1061487','刘辉','2010-05-06 07:20:05.000','2010-05-06 16:32:26.000' union
    select '2010-05-07 00:00:00.000','1061392','刘辉','2010-05-07 07:26:59.000','2010-05-07 17:30:52.000' union
    select '2010-05-08 00:00:00.000','1061487','刘辉','2010-05-08 07:20:05.000','2010-05-08 17:32:26.000'
    )
    go
    select datediff(hour,Convert(varchar(10),COL1-1,120)+' '+CONVERT(varchar(12) , COL5, 108 ),
    Convert(varchar(10),COL1,120)+' '+CONVERT(varchar(12) , COL4, 108 ))
     from checkwork group by COL1,COL2,COL4,COL5
    go
    需求说明
    表中有日期，工号，上班第一次考勤与最后一次考勤，同一天只有一条考勤记录，求两天之前的打卡时间差的小时数
    （同一工号，前一天的最后一次考勤同次日的第一次考勤时间差）。
      

  5.   


    --你给的数据似乎有问题
    --生成测试数据
    create table tb(col1 datetime,col2 varchar(20),col3 varchar(20),col4 datetime,col5 datetime)
    insert into tb
    select '2010-05-05 00:00:00.000', '1000011', '张红', '1900-01-01 07:22:57.000', '1900-01-01 19:31:46.000' union all
    select '2010-05-06 00:00:00.000', '1000011', '张红', '1900-01-01 07:21:17.000', '1900-01-01 19:30:30.000'union all
    select '2010-05-07 00:00:00.000', '1000011', '张红', '1900-01-01 07:22:50.000', '1900-01-01 19:30:46.000'union all
    select '2010-05-08 00:00:00.000', '1000011', '张红', '1900-01-01 07:23:17.000', '1900-01-01 19:30:50.000'union all
    select '2010-05-05 00:00:00.000', '1061392', '刘辉', '1900-01-01 07:26:59.000', '1900-01-01 17:33:52.000'union all
    select '2010-05-06 00:00:00.000', '1061487', '刘辉', '1900-01-01 07:20:05.000', '1900-01-01 16:32:26.000'union all
    select '2010-05-07 00:00:00.000', '1061392', '刘辉', '1900-01-01 07:26:59.000', '1900-01-01 17:30:52.000'union all
    select '2010-05-08 00:00:00.000', '1061487', '刘辉', '1900-01-01 07:20:05.000', '1900-01-01 17:32:26.000'
    --把你给的数据处理一下
    update tb
    set col4=
    substring(convert(varchar(20),col1,120),1,10)
    +''
    +str(datepart(hh,col4))
    +':'
    +str(datepart(mi,col4))
    +':'
    +str(datepart(ss,col4))update tb
    set col5=
    substring(convert(varchar(20),col1,120),1,10)
    +''
    +str(datepart(hh,col5))
    +':'
    +str(datepart(mi,col5))
    +':'
    +str(datepart(ss,col5))--数据变为
    select * from tb
    /*
    2010-05-05 00:00:00.000 1000011 张红 2010-05-05 07:22:57.000 2010-05-05 19:31:46.000
    2010-05-06 00:00:00.000 1000011 张红 2010-05-06 07:21:17.000 2010-05-06 19:30:30.000
    2010-05-07 00:00:00.000 1000011 张红 2010-05-07 07:22:50.000 2010-05-07 19:30:46.000
    2010-05-08 00:00:00.000 1000011 张红 2010-05-08 07:23:17.000 2010-05-08 19:30:50.000
    2010-05-05 00:00:00.000 1061392 刘辉 2010-05-05 07:26:59.000 2010-05-05 17:33:52.000
    2010-05-06 00:00:00.000 1061487 刘辉 2010-05-06 07:20:05.000 2010-05-06 16:32:26.000
    2010-05-07 00:00:00.000 1061392 刘辉 2010-05-07 07:26:59.000 2010-05-07 17:30:52.000
    2010-05-08 00:00:00.000 1061487 刘辉 2010-05-08 07:20:05.000 2010-05-08 17:32:26.000
    */
    --查询select a.col1,a.col3,
    时间差=datediff
    (
    hh,
    a.col5,
    b.col4
    )
    from tb a,tb b
    where a.col3=b.col3
    and datediff(dd,a.col4,b.col4)=1
    order by a.col1--查询结果
    2010-05-05 00:00:00.000 张红 12
    2010-05-05 00:00:00.000 刘辉 14
    2010-05-06 00:00:00.000 刘辉 15
    2010-05-06 00:00:00.000 张红 12
    2010-05-07 00:00:00.000 张红 12
    2010-05-07 00:00:00.000 刘辉 14