MySQL 从同一个表中进行多重选择

Question

我正在构建一个巴士预订系统。我正在尝试根据所选行程查询巴士。我在出发和到达表中存储时间。我需要查询出发和到达。

下面是我的表架构

CREATE TABLE `bus_details` (
  `ID` int(11) NOT NULL,
  `Route` varchar(60) NOT NULL,
  `RouteCode` int(11) NOT NULL,
  `BusCode` int(11) NOT NULL,
  `CityCode` int(11) NOT NULL,
  `City` varchar(20) NOT NULL,
  `Departure` time DEFAULT NULL,
  `Arrival` time DEFAULT NULL,
  `FromCityCode` int(11) NOT NULL,
  `ToCityCode` int(11) NOT NULL,
  `BusName` varchar(30) NOT NULL,
  `sValid` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bus_details` (`ID`, `Route`, `RouteCode`, `BusCode`, `CityCode`, `City`, `Departure`, `Arrival`, `FromCityCode`, `ToCityCode`, `BusName`, `sValid`) VALUES
(48, 'Accra Mall - Papaye', 10001, 1001, 101, 'Accra Mall', '01:00:00', NULL, 101, 101, 'Sprinter', 1),
(49, 'Accra Mall - Papaye', 10001, 1001, 102, 'Flower Pot', '00:30:00', '01:15:00', 101, 102, 'Sprinter', 0),
(50, 'Accra Mall - Papaye', 10001, 1001, 103, 'Palace', '02:00:00', '00:45:00', 102, 103, 'Sprinter', 0),
(51, 'Accra Mall - Papaye', 10001, 1001, 104, 'Papaye', NULL, '02:30:00', 103, 104, 'Sprinter', 1),
(52, 'Accra Mall - Papaye', 10001, 1003, 101, 'Accra Mall', '02:00:00', NULL, 101, 101, 'VVIP Bus', 1),
(53, 'Accra Mall - Papaye', 10001, 1003, 102, 'Flower Pot', '02:30:00', '02:15:00', 101, 102, 'VVIP Bus', 0),
(54, 'Accra Mall - Papaye', 10001, 1003, 103, 'Palace', '03:00:00', '02:45:00', 102, 103, 'VVIP Bus', 0),
(55, 'Accra Mall - Papaye', 10001, 1003, 104, 'Papaye', NULL, '03:15:00', 103, 104, 'VVIP Bus', 1);

ALTER TABLE `bus_details`
  ADD PRIMARY KEY (`ID`);

我尝试过

SELECT DISTINCT(t1.BusCode), t1.BusName, t1.CityCode, t1.FromCityCode, t2.ToCityCode, t1.Departure, t2.Arrival
FROM
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Departure From bus_details Where CityCode IN(101) AND FromCityCode IN(101) Group By BusCode) As t1,
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Arrival From bus_details Where CityCode IN(104) AND ToCityCode IN(104) Group By BusCode) As t2

这接近我的预期答案，但我返回了 4 个结果，正如我预期的那样，因为这次行程只有两辆巴士。

在四个结果中，两个正确，两个不正确。

请您帮我正确查询此操作。

提前谢谢

**Expected Output**
BusName   | tripFrom | tripTo | Departure | Arrival 
Sprinter     101          104    1:00:00    2:30:00    
VVIP Bus     101          104    2:30:00    3:15:00 

这是我想要的输出的示例。再次感谢

Answer 1

您的查询的问题在于您使用的是无条件的 JOIN，因此它会创建 2 条巴士路线 x 2 条巴士路线 = 4 个结果的叉积。如果您有 3 条路线，您将得到 9 个结果。如果您在 SELECT 中包含了 t2.BusName，您就会看到它与 t1.BusName 不同的所有情况。您需要通过添加一个条件来限制结果，以确保两条路线上的总线相同，即 t1.BusCode = t2.BusCode (或 t1.BusName = t2.BusName) >)

SELECT t1.BusName, t1.FromCityCode AS tripFrom, t2.ToCityCode AS tripTo, t1.Departure, t2.Arrival
FROM
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Departure 
     FROM bus_details 
     WHERE CityCode IN(101) AND FromCityCode IN(101) 
     GROUP BY BusCode) As t1
JOIN
    (SELECT BusName, BusCode, CityCode, FromCityCode, ToCityCode, Arrival 
     FROM bus_details 
     WHERE CityCode IN(104) AND ToCityCode IN(104)
     GROUP BY BusCode) As t2
ON t1.BusCode = t2.BusCode

输出(Demo):

BusName     tripFrom    tripTo  Departure   Arrival
Sprinter    101         104     01:00:00    02:30:00
VVIP Bus    101         104     02:00:00    03:15:00