mysql同表多选

我正在建立一个公共汽车预订系统。我正试图根据所选的行程查询出一辆公共汽车。我把出发和到达的时间都放在table上。我需要查询一下出发时间和到达时间。
下面是我的表格模式

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

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

查询的问题是您使用的是 JOIN 如果没有条件，则创建2条公交线路x 2条公交线路=4个结果的叉积。如果你有3条路线，你会得到9个结果。如果你把 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

输出（演示）：

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

mysql同表多选

1条答案

相关问题

热门标签

最新问答