create table 车辆出入记录(车辆编号 CHAR(10),进出标志 CHAR(10),车类 CHAR(10),开始时间 DATETIME,结束时间 DATETIME) insert into 车辆出入记录 (车辆编号,进出标志,车类,开始时间,结束时间)
select '5300100004', 'i', 'p ' ,'2008-01-01','2008-01-01'
union all select '5300100004', 'o', 'x ' ,'2008-01-02','2008-01-02'
union all select '5300100004', 'i', 'x ' ,'2008-01-03','2008-01-03'
union all select '5300100004', 'o', 'x ' ,'2008-01-04','2008-01-04'
union all select '5300100004', 'i', 'x ' ,'2008-01-05','2008-01-05'
union all select '5300100004', 'o', 'p ' ,'2008-01-06','2008-01-06'
union all select '5300100004', 'i', 'x ' ,'2008-01-07','2008-01-07'
union all select '5300100004', 'o', 'p ' ,'2008-01-08','2008-01-08'
union all select '5300100004', 'i', 'p ' ,'2008-01-09','2008-01-09'
union all select '5300100004', 'o', 'x ' ,'2008-01-10','2008-01-10'
union all select '5300100004', 'i', 'x ' ,'2008-01-11','2008-01-11'
union all select '5300100004', 'o', 'p ' ,'2008-01-12','2008-01-12'
SELECT count(*) FROM 车辆出入记录 a left join 车辆出入记录 b
on dateadd(dd,1,a.开始时间)=b.开始时间
WHERE a.进出标志='i' and b.进出标志='o' and (a.车类='x' AND b.车类='x')
select '5300100004', 'i', 'p ' ,'2008-01-01','2008-01-01'
union all select '5300100004', 'o', 'x ' ,'2008-01-02','2008-01-02'
union all select '5300100004', 'i', 'x ' ,'2008-01-03','2008-01-03'
union all select '5300100004', 'o', 'x ' ,'2008-01-04','2008-01-04'
union all select '5300100004', 'i', 'x ' ,'2008-01-05','2008-01-05'
union all select '5300100004', 'o', 'p ' ,'2008-01-06','2008-01-06'
union all select '5300100004', 'i', 'x ' ,'2008-01-07','2008-01-07'
union all select '5300100004', 'o', 'p ' ,'2008-01-08','2008-01-08'
union all select '5300100004', 'i', 'p ' ,'2008-01-09','2008-01-09'
union all select '5300100004', 'o', 'x ' ,'2008-01-10','2008-01-10'
union all select '5300100004', 'i', 'x ' ,'2008-01-11','2008-01-11'
union all select '5300100004', 'o', 'p ' ,'2008-01-12','2008-01-12'
SELECT count(*) FROM 车辆出入记录 a left join 车辆出入记录 b
on dateadd(dd,1,a.开始时间)=b.开始时间
WHERE a.进出标志='i' and b.进出标志='o' and (a.车类='x' AND b.车类='x')
select '5300100004', 'i', 'p ' ,'2008-01-01','2008-01-01'
union all select '5300100004', 'o', 'x ' ,'2008-01-02','2008-01-02'
union all select '5300100004', 'i', 'x ' ,'2008-01-03','2008-01-03'
union all select '5300100004', 'o', 'x ' ,'2008-01-04','2008-01-04'
union all select '5300100004', 'i', 'x ' ,'2008-01-05','2008-01-05'
union all select '5300100004', 'o', 'p ' ,'2008-01-06','2008-01-06'
union all select '5300100004', 'i', 'x ' ,'2008-01-07','2008-01-07'
union all select '5300100004', 'o', 'p ' ,'2008-01-08','2008-01-08'
union all select '5300100004', 'i', 'p ' ,'2008-01-09','2008-01-09'
union all select '5300100004', 'o', 'x ' ,'2008-01-10','2008-01-10'
union all select '5300100004', 'i', 'x ' ,'2008-01-11','2008-01-11'
union all select '5300100004', 'o', 'p ' ,'2008-01-12','2008-01-12' select id=identity(int,1,1),* into # from truck_record order by truckno,from_dateselect a.truckno,sum(case when a.sts='x' and b.sts='x' then 1 else 0 end) as times
from # a left join # b on a.id=b.id-1
where a.in_out='i'
group by a.trucknodrop table #
drop table truck_record