求高手帮忙解决 下面情况下 SQL 语句如下图的2张表，有几个特性。1.两张表的carNum（车牌号） 都是同一个值2.D
求高手帮忙解决 下面情况下 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 行受影响)*/