公交路线查询MySQL语句求助：中途站点查询无结果

问题描述 #

开发公交路线查询应用时，无法查询中途站点间的公交线路。例如某公交运行路线为A站- D站，查询A站- B站时无结果。现有数据库结构及错误查询语句如下：

数据库结构 #

CREATE TABLE `buses` (
  `id` int(11) NOT NULL,
  `bus_name` varchar(50) NOT NULL,
  `cid` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `buses` (`id`, `bus_name`, `cid`, `created_at`, `updated_at`) VALUES
(1, 'BUS 1', 4, '2023-08-03 06:56:56', '2023-07-27 11:02:05'),
(2, 'BUS 2', 8, '2023-08-03 06:56:59', '2023-07-29 02:32:30');

CREATE TABLE `bus_category` (
  `id` int(11) NOT NULL,
  `cname` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `bus_category` (`id`, `cname`, `created_at`, `updated_at`) VALUES
(4, 'NMMT', '2023-07-28 02:34:00', '2023-07-27 08:39:02'),
(5, 'MSRD', '2023-07-28 02:34:05', '2023-07-27 08:39:09'),
(6, 'ST-M', '2023-07-28 02:34:05', '2023-07-27 08:39:09'),
(7, 'MHRY', '2023-07-28 02:34:05', '2023-07-27 08:39:09'),
(8, 'MSEERT2', '2023-07-29 08:02:10', '2023-07-29 02:32:10');

CREATE TABLE `bus_routes` (
  `route_id` int(11) NOT NULL,
  `bus_id` int(11) NOT NULL,
  `start_station_id` int(11) NOT NULL,
  `end_station_id` int(11) NOT NULL,
  `departure_time` varchar(100) NOT NULL,
  `arrival_time` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `bus_routes` (`route_id`, `bus_id`, `start_station_id`, `end_station_id`, `departure_time`, `arrival_time`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 4, '1:00 PM', '2:00 PM', '2023-08-03 07:16:06', '0000-00-00 00:00:00'),
(2, 2, 4, 6, '02:00', '3:00', '2023-08-03 07:16:44', '2023-07-31 07:01:05');

CREATE TABLE `bus_stations` (
  `station_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `bus_stations` (`station_id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'A', '2023-08-03 06:57:57', '0000-00-00 00:00:00'),
(2, 'B', '2023-08-03 06:58:02', '0000-00-00 00:00:00'),
(3, 'C', '2023-07-22 15:22:53', '0000-00-00 00:00:00'),
(4, 'D', '2023-07-22 15:22:53', '0000-00-00 00:00:00'),
(5, 'E', '2023-08-03 06:58:11', '0000-00-00 00:00:00'),
(6, 'F', '2023-07-22 15:22:53', '0000-00-00 00:00:00');

CREATE TABLE `bus_stopovers` (
  `stopover_id` int(11) NOT NULL,
  `route_id` int(11) NOT NULL,
  `station_id` int(11) NOT NULL,
  `stop_time` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `bus_stopovers` (`stopover_id`, `route_id`, `station_id`, `stop_time`, `created_at`, `updated_at`) VALUES
(0, 1, 1, '1:00', '2023-08-03 07:41:27', NULL),
(1, 1, 2, '1:02', '2023-08-03 07:13:45', NULL),
(2, 1, 3, '1:05', '2023-08-03 07:13:54', NULL),
(59, 1, 4, '1:10', '2023-08-03 07:15:51', NULL),
(61, 2, 5, '2:15', '2023-08-03 07:15:51', NULL),
(62, 2, 6, '3:00', '2023-08-03 07:15:51', NULL);

错误查询语句 #

SELECT DISTINCT
    b.bus_name,
    bs_start.name as start,
    bs_end.name as end,
    bs_station.name as stop,
    s.stop_time
FROM bus_routes r
JOIN buses b ON r.bus_id = b.id
JOIN bus_stopovers s ON r.route_id = s.route_id
JOIN bus_stations bs_start ON r.start_station_id = bs_start.station_id
JOIN bus_stations bs_end ON  r.end_station_id = bs_end.station_id
JOIN bus_stations bs_station ON s.station_id = bs_station.station_id
WHERE bs_start.name="A" AND bs_end.name="B"
ORDER BY  r.departure_time;

问题原因 #

原查询的WHERE条件错误地将bus_routes表中的起点和终点直接匹配用户输入的站点，但用户要查的是中途站点（比如B站是BUS1路线的中途站，不是该路线的终点），因此该条件无法命中任何数据。

正确查询语句 #

要实现中途站点查询，需要先找到同时包含用户起点和终点的路线，并且确保起点在路线中的停靠顺序早于终点。以下是正确的SQL：

SELECT DISTINCT
    b.bus_name,
    start_station.name AS user_start,
    end_station.name AS user_end,
    r.departure_time AS route_departure,
    start_stop.stop_time AS start_stop_time,
    end_stop.stop_time AS end_stop_time
FROM bus_routes r
JOIN buses b ON r.bus_id = b.id
-- 关联起点站的停靠信息
JOIN bus_stopovers start_stop ON r.route_id = start_stop.route_id
JOIN bus_stations start_station ON start_stop.station_id = start_station.station_id
-- 关联终点站的停靠信息
JOIN bus_stopovers end_stop ON r.route_id = end_stop.route_id
JOIN bus_stations end_station ON end_stop.station_id = end_station.station_id
-- 筛选用户输入的起点和终点，且起点停靠时间早于终点
WHERE start_station.name = "A" 
  AND end_station.name = "B"
  -- 这里假设stop_time是可比较的时间格式，若格式不统一需要转换
  AND STR_TO_DATE(start_stop.stop_time, '%h:%i') < STR_TO_DATE(end_stop.stop_time, '%h:%i')
ORDER BY r.departure_time;

说明 #

• 该查询通过两次关联bus_stopovers表，分别获取用户起点和终点在路线中的停靠记录。
• 使用STR_TO_DATE将字符串格式的停靠时间转换为可比较的日期类型，确保起点在终点之前停靠。如果你的时间格式包含AM/PM，需要调整格式符为'%h:%i %p'。
• 最终返回公交名称、用户查询的起止站点、路线发车时间、起点停靠时间和终点停靠时间，满足中途站点查询需求。

内容的提问来源于stack exchange，提问作者TeeTask TeeTask