select ScheduleCode as 车次,TrainType as 车次信息,FromCity as 出发站点,Tocity as 到达站点,
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in select ScheduleCode from T_Schedule_Details where CityName='天津')select ScheduleCode from T_Schedule_Details where CityName='南昌'
首先想要分别查询出后面2条语句的ScheduleCode ,然后在2调语句的结果中找出相等的数据(可能有多条),然后查找出的匹配数据作为蓝色语句子查询的条件
进行查询 ,不知道该语句应该如何组合.求解
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in select ScheduleCode from T_Schedule_Details where CityName='天津')select ScheduleCode from T_Schedule_Details where CityName='南昌'
首先想要分别查询出后面2条语句的ScheduleCode ,然后在2调语句的结果中找出相等的数据(可能有多条),然后查找出的匹配数据作为蓝色语句子查询的条件
进行查询 ,不知道该语句应该如何组合.求解
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in select ScheduleCode from T_Schedule_Details where CityName ='天津' and CityName='南昌'
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (select ScheduleCode from T_Schedule_Details where CityName ='天津' and CityName='南昌')
select ScheduleCode from T_Schedule_Details where CityName='南昌'
分开单独执行的话 有 1对匹配数据,但是按照楼上朋友的语句来查询 则没有返回任何结果,只有列名
select ScheduleCode as 车次,TrainType as 车次信息,FromCity as 出发站点,Tocity as 到达站点,
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in select ScheduleCode from T_Schedule_Details where CityName ='天津' or CityName='南昌'
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (select ScheduleCode from T_Schedule_Details where CityName='天津' or CityName='南昌')
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数
from T_Schedule A join (select distinct ScheduleCode from T_Schedule_Details where CityName='天津' or CityName='南昌')B
on A.ScheduleCode=B.ScheduleCode
车次编码 -始发站 --终点站 --驶出时间 ---到达时间-----------列车类型-----------------列车时速------车速--------列车从起点至终于所用天数
1472 哈尔滨 徐州 20:57 22:16 普快(哈尔滨-徐州) 100/公里 1940 2
T167 北京西 南昌 18:02 07:46 空提特快(北京西-南昌) 110/公里 1584 2
T188 沈阳北 深圳 17:50 05:18 空提特快(沈阳北-深圳) 125/公里 3152 3
这是T_Schedule_Details表中的部分数据列车到站的编号--做为T_Schedule的外键--途径的车站--到该车站时间--驶出该车站时间--该站是该车次所途径的第几站--从上站至该站距离--从初始站至该站所用天数
1 T188 沈阳 17:57 18:05 1 3 1
2 T188 鞍山 19:03 19:07 2 67 1
3 T188 沟帮子 21:14 21:20 3 236 1
4 T188 山海关 23:59 00:07 4 484 2
5 T188 天津 03:09 03:18 5 785 2
6 T188 衡水 06:39 06:45 6 1054 2
7 T188 聊城 08:18 08:33 7 1206 2
8 T188 商丘南 11:05 11:09 8 1467 2
9 T188 麻城 15:02 15:08 9 1871 2
10 T188 九江 17:08 17:02 10 2094 2
11 T188 南昌 18:40 18:49 11 2229 2
12 T188 赣州 23:15 23:20 12 2641 2
13 T188 惠州 03:45 03:47 13 3037 3
14 T188 深圳 05:18 终点站 14 3152 3
29 T188 沈阳北 17:57 18:05 1 3 1
30 T167 北京西 16:02 始发站 1 0 1
31 T167 石家庄 18:33 18:47 2 277 1
32 T167 邯郸 20:08 20:10 3 442 1
33 T167 新乡 21:41 21:43 4 609 1
34 T167 郑州 22:32 22:35 5 689 1
35 T167 许昌 21:19 23:21 6 775 1
36 T167 漯河 23:55 23:57 7 829 1
37 T167 信阳 01:22 01:24 8 991 2
38 T167 华容 04:36 04:38 9 1285 2
39 T167 黄石 05:08 05:10 10 1334 2
40 T167 南昌 07:46 终点站 11 1584 2
select ScheduleCode as 车次,TrainType as 车次信息,FromCity as 出发站点,Tocity as 到达站点,
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (
select a.ScheduleCode from (select ScheduleCode from T_Schedule where FromCity = '天津' ) a inner join
(select ScheduleCode from T_Schedule where FromCity = '南昌') b on a.ScheduleCode = b.ScheduleCode)
select ScheduleCode as 车次,TrainType as 车次信息,FromCity as 出发站点,Tocity as 到达站点,
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (
select a.ScheduleCode from (select ScheduleCode from T_Schedule_Details where CityName= '天津' ) a inner join
(select ScheduleCode from T_Schedule_Details where CityName= '南昌') b on a.ScheduleCode = b.ScheduleCode and a.站次<b.站次)
select ScheduleCode as 车次,TrainType as 车次信息,FromCity as 出发站点,Tocity as 到达站点,
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (select ScheduleCode from T_Schedule_Details where CityName='天津' and CityName='南昌')
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (
select a.ScheduleCode from (select ScheduleCode from T_Schedule_Details where CityName= '天津' ) a inner join
(select ScheduleCode from T_Schedule_Details where CityName= '南昌') b on a.ScheduleCode = b.ScheduleCode and a.站次 <b.站次 ) 16 楼朋友的“站次从何而来”?
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (
select a.ScheduleCode from (select ScheduleCode from T_Schedule_Details where CityName= '天津' ) a inner join
(select ScheduleCode from T_Schedule_Details where CityName= '南昌') b on a.ScheduleCode = b.ScheduleCode and a.站次 <b.站次 )
16楼朋友的答案我看了,还是有很多问题 不知道你有没有测试一下?
if object_id('T_Schedule') is not null drop table T_Schedule
create table T_Schedule(ScheduleCode varchar(10),FromCity varchar(10),Tocity varchar(10),
LeaveTime varchar(10),ArrivalTime varchar(10),TrainType varchar(25),Speed varchar(10),Distance int,Days int)
insert into T_Schedule
select '1472','哈尔滨','徐州','20:57','22:16','普快(哈尔滨-徐州)','100/公里',1940, 2 union all
select 'T167','北京西','南昌','18:02','07:46','空提特快(北京西-南昌)','110/公里',1584,2 union all
select 'T188','沈阳北','深圳','17:50','05:18',' 空提特快(沈阳北-深圳)','125/公里',3152,3 if object_id('T_Schedule_Details') is not null drop table T_Schedule_Details
create table T_Schedule_Details(ScheduleCode varchar(10),CityName varchar(10),ArrivalTime varchar(10),LeaveTime varchar(10),NumberCode int,Distance int,Days int)
insert into T_Schedule_Details
select 'T188','沈阳','17:57','18:05',1,3,1 union all
select 'T188','鞍山','19:03','19:07',2,67,1 union all
select 'T188','沟帮子','21:14','21:20',3,236,1 union all
select 'T188','山海关','23:59','00:07',4,484,2 union all
select 'T188','天津','03:09','03:18',5,785,2 union all
select 'T188','衡水','06:39','06:45',6,1054,2 union all
select 'T188','聊城','08:18','08:33',7,1206,2 union all
select 'T188','商丘南','11:05','11:09',8,1467,2 union all
select 'T188','麻城','15:02','15:08',9,1871,2 union all
select 'T188','九江','17:08','17:02',10,2094,2 union all
select 'T188','南昌','18:40','18:49',11,2229,2 union all
select 'T188','赣州','23:15','23:20',12,2641,2 union all
select 'T188','惠州','03:45','03:47',13,3037,3 union all
select 'T188','深圳','05:18','终点站',14,3152,3 union all
select 'T188','沈阳北','17:57','18:05',1,3,1 union all
select 'T167','北京西','16:02','始发站',1,0,1 union all
select 'T167','石家庄','18:33','18:47',2,277,1 union all
select 'T167','邯郸','20:08','20:10',3,442,1 union all
select 'T167','新乡','21:41','21:43',4,609,1 union all
select 'T167','郑州','22:32','22:35',5,689,1 union all
select 'T167','许昌','21:19','23:21',6,775,1 union all
select 'T167','漯河','23:55','23:57',7,829,1 union all
select 'T167','信阳','01:22','01:24',8,991,2 union all
select 'T167','华容','04:36',' 04:38',9,1285,2 union all
select 'T167','黄石','05:08','05:10',10,1334,2 union all
select 'T167','南昌','07:46','终点站',11,1584,2 select ScheduleCode as 车次,TrainType as 车次信息,FromCity as 出发站点,Tocity as 到达站点,
LeaveTime as 开车时间,ArrivalTime as 到达时间,Speed as 时速,Distance as 到达距离,
Days as 所用天数 from T_Schedule
where ScheduleCode in (
select a.ScheduleCode from (select ScheduleCode,NumberCode from T_Schedule_Details where CityName= '天津' ) a inner join
(select ScheduleCode,NumberCode from T_Schedule_Details where CityName= '南昌') b on a.ScheduleCode = b.ScheduleCode and a.NumberCode <b.NumberCode ) 车次 车次信息 出发站点 到达站点 开车时间 到达时间 时速 到达距离 所用天数
---------- ------------------------- ---------- ---------- ---------- ---------- ---------- ----------- -----------
T188 空提特快(沈阳北-深圳) 沈阳北 深圳 17:50 05:18 125/公里 3152 3(所影响的行数为 1 行)