create table bb(id int,card varchar(10),rq varchar(2),in1 datetime,out1 datetime)
go
insert into bb(id ,card ,rq ,in1 ,out1 )
select 2915,'810','01','2008-11-01 18:00:00.000','2008-11-02 04:00:00.000'
union all
select 2915,'810','02','2008-11-02 18:00:00.000','2008-11-03 04:00:00.000'
union all
select 2915,'810','03','2008-11-03 18:00:00.000','2008-11-04 04:00:00.000'
union all
select 2915,'810','03','2008-11-04 18:00:00.000','2008-11-05 04:00:00.000' create table ss(card varchar(10),rq varchar(2),sktime varchar(5))
go
insert into ss(card,rq,sktime)
select '810','01','18:00'
union all
select '810','01','22:00'
union all
select '810','01','23:00'
union all
select '810','01','12:00'
union all
select '810','01','19:00'
union all
select '810','02','18:03'
union all
select '810','02','01:00'
union all
select '810','02','20:00'
union all
select '810','03','12:00'
union all
select '810','03','19:00'
union all
select '810','03','01:00'
union all
select '810','03','02:00'
union all
select '810','04','18:00'
union all
select '810','04','00:00'
union all
select '810','04','01:00' ----SQL2000
条件在bb表里的in1,out1的时间范围内找出ss表里对应的日期内找出符合的刷卡数据,现在我要的结果就是:
id card, rq, sk,
2915 810 '01' '18:00'
2915 810 '01' '19:00'
2915 810 '01' '22:00'
2915 810 '01' '23:00'
2915 810 '02' '01:00'
2915 810 '02' '18:03'
2915 810 '02' '20:00'
2915 810 '03' '01:00'
2915 810 '03' '02:00'
2915 810 '03' '19:00'
2915 810 '04' '01:00'
2915 810 '04' '18:00'
go
insert into bb(id ,card ,rq ,in1 ,out1 )
select 2915,'810','01','2008-11-01 18:00:00.000','2008-11-02 04:00:00.000'
union all
select 2915,'810','02','2008-11-02 18:00:00.000','2008-11-03 04:00:00.000'
union all
select 2915,'810','03','2008-11-03 18:00:00.000','2008-11-04 04:00:00.000'
union all
select 2915,'810','03','2008-11-04 18:00:00.000','2008-11-05 04:00:00.000' create table ss(card varchar(10),rq varchar(2),sktime varchar(5))
go
insert into ss(card,rq,sktime)
select '810','01','18:00'
union all
select '810','01','22:00'
union all
select '810','01','23:00'
union all
select '810','01','12:00'
union all
select '810','01','19:00'
union all
select '810','02','18:03'
union all
select '810','02','01:00'
union all
select '810','02','20:00'
union all
select '810','03','12:00'
union all
select '810','03','19:00'
union all
select '810','03','01:00'
union all
select '810','03','02:00'
union all
select '810','04','18:00'
union all
select '810','04','00:00'
union all
select '810','04','01:00' ----SQL2000
条件在bb表里的in1,out1的时间范围内找出ss表里对应的日期内找出符合的刷卡数据,现在我要的结果就是:
id card, rq, sk,
2915 810 '01' '18:00'
2915 810 '01' '19:00'
2915 810 '01' '22:00'
2915 810 '01' '23:00'
2915 810 '02' '01:00'
2915 810 '02' '18:03'
2915 810 '02' '20:00'
2915 810 '03' '01:00'
2915 810 '03' '02:00'
2915 810 '03' '19:00'
2915 810 '04' '01:00'
2915 810 '04' '18:00'
go
insert into bb(id ,card ,rq ,in1 ,out1 )
select 2915,'810','01','2008-11-01 18:00:00.000','2008-11-02 04:00:00.000'
union all
select 2915,'810','02','2008-11-02 18:00:00.000','2008-11-03 04:00:00.000'
union all
select 2915,'810','03','2008-11-03 18:00:00.000','2008-11-04 04:00:00.000'
union all
select 2915,'810','03','2008-11-04 18:00:00.000','2008-11-05 04:00:00.000' create table ss(card varchar(10),rq varchar(2),sktime varchar(5))
go
insert into ss(card,rq,sktime)
select '810','01','18:00'
union all
select '810','01','22:00'
union all
select '810','01','23:00'
union all
select '810','01','12:00'
union all
select '810','01','19:00'
union all
select '810','02','18:03'
union all
select '810','02','01:00'
union all
select '810','02','20:00'
union all
select '810','03','12:00'
union all
select '810','03','19:00'
union all
select '810','03','01:00'
union all
select '810','03','02:00'
union all
select '810','04','18:00'
union all
select '810','04','00:00'
union all
select '810','04','01:00' select a.* from ss a join bb b on a.sktime between convert(varchar(5),b.out1,8) and convert(varchar(5),b.in1,8)drop table bb,ss
card rq sktime
---------- ---- ------
810 01 18:00
810 01 12:00
810 03 12:00
810 04 18:00
810 01 18:00
810 01 12:00
810 03 12:00
810 04 18:00
810 01 18:00
810 01 12:00
810 03 12:00
810 04 18:00
810 01 18:00
810 01 12:00
810 03 12:00
810 04 18:00(16 行受影响)
go
insert into bb(id ,card ,rq ,in1 ,out1 )
select 2915,'810','01','2008-11-01 18:00:00.000','2008-11-02 04:00:00.000'
union all
select 2915,'810','02','2008-11-02 18:00:00.000','2008-11-03 04:00:00.000'
union all
select 2915,'810','03','2008-11-03 18:00:00.000','2008-11-04 04:00:00.000'
union all
select 2915,'810','03','2008-11-04 18:00:00.000','2008-11-05 04:00:00.000' create table ss(card varchar(10),rq varchar(2),sktime varchar(5))
go
insert into ss(card,rq,sktime)
select '810','01','18:00'
union all
select '810','01','22:00'
union all
select '810','01','23:00'
union all
select '810','01','12:00'
union all
select '810','01','19:00'
union all
select '810','02','18:03'
union all
select '810','02','01:00'
union all
select '810','02','20:00'
union all
select '810','03','12:00'
union all
select '810','03','19:00'
union all
select '810','03','01:00'
union all
select '810','03','02:00'
union all
select '810','04','18:00'
union all
select '810','04','00:00'
union all
select '810','04','01:00' select distinct a.* from ss a join bb b on a.sktime between convert(varchar(5),b.in1,8) and '24:00' or a.sktime between '00:00' and convert(varchar(5),b.out1,8)drop table bb,sscard rq sktime
---------- ---- ------
810 01 18:00
810 01 19:00
810 01 22:00
810 01 23:00
810 02 01:00
810 02 18:03
810 02 20:00
810 03 01:00
810 03 02:00
810 03 19:00
810 04 00:00
810 04 01:00
810 04 18:00(13 行受影响)改下,应该这样