前言
众所周知,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';