sql查询语句难点--以火车时刻查询为例

前言

众所周知，12306的火车购票系统是国内并发量最大，逻辑复杂度极高。本次sql语句以查询火车来模拟。

一、数据库表结构

1.火车表结构

火车表主要信息包括：列车编号、运行里程、运行时间、列车类型。
自建表结构如下图：

2.火车类型表结构

火车类型表较为简单，有车型名称及编号。

3.站点表结构

站点表较为简单，仅包括站点名称。
表结构如下图：

4.火车过站表结构

火车过站表作为火车与站点的中间联系表最为关键，主要信息有：火车id、站点id、站点发车时间、站点火车到站时间、距离上一站里程及火车在此站的停车时间。

二、查询语句

1.以火车表为主表查询所有主要信息

火车表内联查询火车类型表、站点表、火车过站表

SELECT
train.id,
train.carno,
train.distance,
train.runtime,
train.carttypeid,
cartype.id,
cartype.typename,
cartype.code,
after_siten.id,
after_siten.trainid,
after_siten.sitenid,
after_siten.starttime,
after_siten.endtime,
after_siten.distance,
after_siten.stoptime,
siten.id,
siten.sitename
FROM
train
INNER JOIN cartype ON train.carttypeid = cartype.id
INNER JOIN after_siten ON train.id = after_siten.trainid
INNER JOIN siten ON after_siten.sitenid = siten.id
WHERE
after_siten.starttime=null 
or after_siten.endtime=null

2.聚合查询

查询火车起始站、重点站及列车出发时间、到达终点站时间
思路：
1.利用starttime与endtime的null判断起点、终点站，并作为查询结果的新列；
2.按列车特征进行分组，得到列车信息

-- 聚合分组
SELECT t.`carno`,c.`typename`,t.`runtime`,
MAX(IF(a.`endtime` IS NULL, s.`sitename`, NULL)) 起点站,
MAX(IF(a.`starttime` IS NULL, s.`sitename`, NULL)) 终点站,
MAX(IF(a.`endtime` IS NULL, a.`starttime`, NULL)) 发车时间,
MAX(IF(a.`starttime` IS NULL, a.`endtime`, NULL)) 到站时间
 FROM after_siten a
LEFT JOIN train t ON a.`trainid`=t.`id`
LEFT JOIN cartype c ON t.`carttypeid`=c.`id`
LEFT JOIN siten s ON a.`sitenid`=s.`id`
GROUP BY t.`carno`,c.`typename`,t.`runtime`
HAVING 起点站='北京西'

3.查询站点过路车

思路：根据列车id分组，将列车经过的站点连接在后面

-- 过路车
SELECT a.`trainid`,GROUP_CONCAT(a.`sitenid` ORDER BY a.id) glc
FROM after_siten a 
WHERE a.`sitenid` IN(3, 4)
GROUP BY a.`trainid`
HAVING glc='4,3';