求高手帮忙解决 下面情况下 SQL 语句

如下图的2张表，有几个特性。
1.两张表的carNum（车牌号） 都是同一个值
2.DepartureTime 这张表 的字段departureTime2 和字段 departureTime 的时间是相同的，只是departureTime2 多了年月日
3.departureTime2 字段和GpsTime 字段的 年月日也是相同的，只是时间不同而已。
4.inOut 字段意思是 进站（用1表示）、出站（用0表示），regionId 为公交站点ID


问：
我现在要组合成一张表，表结构类型
departureTime carNum InTime OutTime RegionName
08:15 闽B02929 2011-11-28 8:10:15 2011-11-28 08:15:30 钟楼站
09:15 闽B02929 2011-11-28 9:10:15 2011-11-28 09:15:30 水部门兜
10:15 。。。。
11:15 。。。
12:15 。。。

附件：

 
创建2张表脚本。
SQL code

CREATE TABLE [dbo].[DepartureTime](
    [departureTime] [varchar](50) NULL,
    [departureTime2] [datetime] NULL,
    [carNum] [varchar](50) NULL
) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[RecordTable](
    [carId] [int] NULL,
    [carNum] [varchar](50) NULL,
    [GpsTime] [datetime] NULL,
    [inOut] [int] NULL,
    [regionId] [int] NULL,
    [RegionName] [varchar](50) NULL
) ON [PRIMARY]

GO

SQL code

CREATE TABLE [dbo].[DepartureTime](
    [departureTime] [varchar](50) NULL,
    [departureTime2] [datetime] NULL,
    [carNum] [varchar](50) NULL
) ON [PRIMARY]
insert into DepartureTime select '08:15','2011-11-28 08:15:00','闽B02929'
insert into DepartureTime select '09:15','2011-11-28 09:15:00','闽B02929'
insert into DepartureTime select '10:15','2011-11-28 10:15:00','闽B02929'
insert into DepartureTime select '11:15','2011-11-28 11:15:00','闽B02929'
insert into DepartureTime select '12:15','2011-11-28 12:15:00','闽B02929'
CREATE TABLE [dbo].[RecordTable](
    [carId] [int] NULL,
    [carNum] [varchar](50) NULL,
    [GpsTime] [datetime] NULL,
    [inOut] [int] NULL,
    [regionId] [int] NULL,
    [RegionName] [varchar](50) NULL
) ON [PRIMARY]
insert into RecordTable select 9517,'闽B02929','2011-11-28 08:10:10',1,2728,'钟楼站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 08:15:10',0,2728,'钟楼站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 09:10:10',1,2751,'水部门兜站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 09:16:10',0,2751,'水部门兜站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 10:10:10',1,2728,'钟楼站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 10:11:10',0,2728,'钟楼站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 11:10:10',1,2751,'水部门兜站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 11:14:10',0,2751,'水部门兜站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 12:10:10',1,2728,'钟楼站'
insert into RecordTable select 9517,'闽B02929','2011-11-28 12:15:10',0,2728,'钟楼站'
go
select a.departureTime,a.carNum,b.GpsTime as InTime,c.GpsTime as OutTime,b.RegionName
from [DepartureTime] a inner join [RecordTable] b on a.carNum=b.carNum and datediff(mi,a.departureTime2,b.GpsTime) between -10 and 10
inner join [RecordTable] c on c.carNum=b.carNum and c.RegionName=b.RegionName and datediff(mi,b.GpsTime,c.GpsTime)between 0 and 10
where b.inOut=1 and c.inOut=0
/*
departureTime   carNum           InTime                  OutTime                 RegionName
--------------- --------------- ------------------------ ----------------------- ----------------------- --------
08:15           闽B02929         2011-11-28 08:10:10.000 2011-11-28 08:15:10.000 钟楼站
09:15           闽B02929         2011-11-28 09:10:10.000 2011-11-28 09:16:10.000 水部门兜站
10:15           闽B02929         2011-11-28 10:10:10.000 2011-11-28 10:11:10.000 钟楼站
11:15           闽B02929         2011-11-28 11:10:10.000 2011-11-28 11:14:10.000 水部门兜站
12:15           闽B02929         2011-11-28 12:10:10.000 2011-11-28 12:15:10.000 钟楼站

(5 行受影响)
*/
go
--drop table RecordTable,DepartureTime

根据楼主的数据,设置了进出站时间在10分钟之内,且同一个站的departureTime2 和进站 GpsTime 时间在正负十分钟之内.
后一时间的设置可以与出站时间去进行对比,即
datediff(mi,a.departureTime2,c.GpsTime) between -10 and 10
这样,时间误差可减小一些,但注意要写到后一个连接的后面,或者干脆直接用where条件.

SQL code

if object_id('DepartureTime','U') is not null
   drop table DepartureTime
go
create table DepartureTime
(
 departureTime varchar(5),
 departureTime2 datetime,
 carNum varchar(10)
)
go
insert into DepartureTime
select '08:15','2011-11-28 08:15:00','闽B02929' union all
select '09:15','2011-11-28 09:15:00','闽B02929' union all
select '10:15','2011-11-28 10:15:00','闽B02929' union all
select '11:15','2011-11-28 11:15:00','闽B02929' union all
select '12:15','2011-11-28 12:15:00','闽B02929'
go
if object_id('Region','U') is not null
   drop table Region
go
create table Region
(
 cardId int,
 carNum varchar(10),
 GpsTime datetime,
 inOut int,
 regionId int,
 RegionName varchar(10)
)
go
insert into Region
select '9517','闽B02929','2011-11-28 08:10:10',1,2728,'钟楼站' union all
select '9517','闽B02929','2011-11-28 08:15:10',0,2728,'钟楼站' union all
select '9517','闽B02929','2011-11-28 09:10:10',1,2751,'水部门兜站' union all
select '9517','闽B02929','2011-11-28 09:16:10',0,2751,'水部门兜站' union all
select '9517','闽B02929','2011-11-28 10:10:10',1,2728,'钟楼站' union all
select '9517','闽B02929','2011-11-28 10:11:10',0,2728,'钟楼站' union all
select '9517','闽B02929','2011-11-28 11:10:10',1,2751,'水部门兜站' union all
select '9517','闽B02929','2011-11-28 11:14:10',0,2751,'水部门兜站' union all
select '9517','闽B02929','2011-11-28 12:10:10',1,2728,'钟楼站' union all
select '9517','闽B02929','2011-11-28 12:15:10',0,2728,'钟楼站'
go
select departureTime,
       carNum,
       InTime=(select GpsTime from Region where convert(varchar(13),GpsTime,120)=convert(varchar(13),a.departureTime2,120) and inOut=1),
       OutTime=(select GpsTime from Region where convert(varchar(13),GpsTime,120)=convert(varchar(13),a.departureTime2,120) and inOut=0),
       RegionName=(select distinct RegionName from Region where convert(varchar(13),GpsTime,120)=convert(varchar(13),a.departureTime2,120))
from DepartureTime a
/*
departureTime carNum     InTime                  OutTime                 RegionName
------------- ---------- ----------------------- ----------------------- ----------
08:15         闽B02929    2011-11-28 08:10:10.000 2011-11-28 08:15:10.000 钟楼站
09:15         闽B02929    2011-11-28 09:10:10.000 2011-11-28 09:16:10.000 水部门兜站
10:15         闽B02929    2011-11-28 10:10:10.000 2011-11-28 10:11:10.000 钟楼站
11:15         闽B02929    2011-11-28 11:10:10.000 2011-11-28 11:14:10.000 水部门兜站
12:15         闽B02929    2011-11-28 12:10:10.000 2011-11-28 12:15:10.000 钟楼站

(5 行受影响)
*/