求高手帮忙解决 下面情况下 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,'钟楼站'goselect a.departureTime,a.carNum,b.GpsTime as InTime,c.GpsTime as OutTime,b.RegionNamefrom [DepartureTime] a inner join [RecordTable] b on a.carNum=b.carNum and datediff(mi,a.departureTime2,b.GpsTime) between -10 and 10inner join [RecordTable] c on c.carNum=b.carNum and c.RegionName=b.RegionName and datediff(mi,b.GpsTime,c.GpsTime)between 0 and 10where 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
------解决方案--------------------
- SQL code
if object_id('DepartureTime','U') is not null drop table DepartureTimegocreate table DepartureTime( departureTime varchar(5), departureTime2 datetime, carNum varchar(10))goinsert into DepartureTimeselect '08:15','2011-11-28 08:15:00','闽B02929' union allselect '09:15','2011-11-28 09:15:00','闽B02929' union allselect '10:15','2011-11-28 10:15:00','闽B02929' union allselect '11:15','2011-11-28 11:15:00','闽B02929' union allselect '12:15','2011-11-28 12:15:00','闽B02929'goif object_id('Region','U') is not null drop table Regiongocreate table Region( cardId int, carNum varchar(10), GpsTime datetime, inOut int, regionId int, RegionName varchar(10))goinsert into Regionselect '9517','闽B02929','2011-11-28 08:10:10',1,2728,'钟楼站' union allselect '9517','闽B02929','2011-11-28 08:15:10',0,2728,'钟楼站' union allselect '9517','闽B02929','2011-11-28 09:10:10',1,2751,'水部门兜站' union allselect '9517','闽B02929','2011-11-28 09:16:10',0,2751,'水部门兜站' union allselect '9517','闽B02929','2011-11-28 10:10:10',1,2728,'钟楼站' union allselect '9517','闽B02929','2011-11-28 10:11:10',0,2728,'钟楼站' union allselect '9517','闽B02929','2011-11-28 11:10:10',1,2751,'水部门兜站' union allselect '9517','闽B02929','2011-11-28 11:14:10',0,2751,'水部门兜站' union allselect '9517','闽B02929','2011-11-28 12:10:10',1,2728,'钟楼站' union allselect '9517','闽B02929','2011-11-28 12:15:10',0,2728,'钟楼站'goselect 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 行受影响)*/