from trainnum t,TrainNumDetails td
where t.trainnum='K2775' and td.tid=t.id
select station 经过站点,arriveTime 到达时间,stayTime 发车时间,
datediff(mi,arriveTime,stayTime) 停留时间
from TrainNumDetails
where tid=1
----------
select t.id 编号,t.trainnum 车次,t.departure 始发站,t.destination 终点站,
td.station 经过站点,td.arriveTime 到达时间,td.startTime 发车时间,
datediff(mi,arriveTime,startTime) 停留时间
from TrainNumDetails td,trainnum t
where td.tid=t.id and t.trainnum='K2775'
and stayTime between '2017-11-07 00:00:00' and '2017-11-07 23:59:59'
select t.id 编号,t.trainNum 车次,t.departure 始发站,
t.destination 终点站,q.stayTime 出发时间,z.arriveTime 到达时间,
datediff(mi,q.stayTime,z.arriveTime) 车程时间
from TrainNum t,TrainNumDetails q,TrainNumDetails z
where q.station='株洲' and z.station='玉山' and q.tid=z.tid
and q.tid=t.id
and q.startTime between '2017-11-07 00:00:00' and '2017-11-07 23:59:59'
如果简单设计,站点表S,线路表L,站点线路中间表S_L,座位SEAT(外键线路表),座位站点中间表SEAT_S(该站点该作为是否有人,冗余线路id)
站点表,线路表,站点线路中间表,座位(外键线路表)数据要初始化
买票时票途径站点1,2,3,那1,2这个站有人,存入座位站点中间表
站点间车次信息,站点表,线路表,站点线路中间表关联即可
它的余票,车次座位总数-该站点该车次有座人数,两个子查询(此处要优化性能的话,可以将数据冗余,车次座位数冗余到车次上,站点线路中间表可以冗余剩余票数,已售票数,数据更新时同步冗余数据,比如买票退票需要更新余票,新增座位同步更新车次座位数)
哪个座位有余票,查询站点在该车次没有售出的座位即可,大概sql,SELECT SEAT.* FROM L,SEAT WHERE L.ID = '线路' AND NOT EXISTS(SELECT 1 FROM SEAT_S WHERE AND SEAT_S.SID = '站点' )