数据库查询查找特定日期两个站之间的火车,如irctc.co.in [英] database query to find trains between two stations on specific date like irctc.co.in
问题描述
我有以下数据库
CREATE TABLE IF NOT EXISTS `trains` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`train_no` varchar(5) COLLATE latin1_general_ci DEFAULT NULL,
`train_name` varchar(50) COLLATE latin1_general_ci NOT NULL,
`runsfrom` varchar(50) COLLATE latin1_general_ci NOT NULL,
`SUN` varchar(3) COLLATE latin1_general_ci NOT NULL,
`MON` varchar(3) COLLATE latin1_general_ci NOT NULL,
`TUE` varchar(3) COLLATE latin1_general_ci NOT NULL,
`WED` varchar(3) COLLATE latin1_general_ci NOT NULL,
`THU` varchar(3) COLLATE latin1_general_ci NOT NULL,
`FRI` varchar(3) COLLATE latin1_general_ci NOT NULL,
`SAT` varchar(3) COLLATE latin1_general_ci NOT NULL,
`DOE` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1912 ;
具有如下数据:
INSERT INTO `trains` VALUES (269, '12307', 'HWH JU EXPRESS', 'HOWRAH JN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
INSERT INTO `trains` VALUES (270, '12308', 'JU HWH SUPFAST', 'JODHPUR JN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
INSERT INTO `trains` VALUES (381, '12461', 'MANDOR EXPRESS', 'DELHI', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
INSERT INTO `trains` VALUES (382, '12462', 'MANDOR EXPRESS', 'JODHPUR JN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN', '2013-03-24');
表的表结构 train_number
Table structure for table train_number
CREATE TABLE IF NOT EXISTS `train_number` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`train_no` varchar(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1912 ;
具有如下数据:
INSERT INTO `train_number` VALUES (269, '12307');
INSERT INTO `train_number` VALUES (270, '12308');
INSERT INTO `train_number` VALUES (381, '12461');
表的表结构 train_schedule
Table structure for table train_schedule
CREATE TABLE IF NOT EXISTS `train_schedule` (
`train_no` varchar(5) NOT NULL,
`stn_code` varchar(20) NOT NULL,
`stn_name` varchar(50) NOT NULL,
`route_no` varchar(2) NOT NULL,
`arr_time` varchar(5) NOT NULL,
`dep_time` varchar(5) NOT NULL,
`halt_time` varchar(5) NOT NULL,
`distance` varchar(4) NOT NULL,
`day` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
具有如下数据:
INSERT INTO `train_schedule` VALUES ('12307', 'HWH ', 'HOWRAH JN ', '1', 'Sourc', '23:30', '', '0', '1');
INSERT INTO `train_schedule` VALUES ('12307', 'BWN ', 'BARDDHAMAN JN ', '1', '00:35', '00:37', '2:00', '95', '2');
INSERT INTO `train_schedule` VALUES ('12307', 'ASN ', 'ASANSOL JN ', '1', '01:52', '01:56', '4:00', '200', '2');
INSERT INTO `train_schedule` VALUES ('12307', 'DHN ', 'DHANBAD JN ', '1', '03:05', '03:15', '10:00', '259', '2');
现在我想找到两个车站之间的火车在特定日期。所以我尝试使用这个查询
Now I want to find to find the trains between two stations on specific date. So I tried with this query
SELECT distinct d1.train_no
FROM train_schedule d1
INNER JOIN train_schedule d2 ON d2.train_no=d1.train_no
WHERE d1.stn_code = 'JU' and d2.stn_code = 'JP'
但它显示了从JU到JP和从JP到JU的数据,所以它使结果双重。
But it's showing both data from JU to JP and from JP to JU also so it makes the result double.
我想做这个查询在数据库中也给出
I want to make this query correct only for one direction on a specific date as days when it runs is also given in database
推荐答案
如果我正确理解你的问题,回答只返回一个方向的部分,请尝试以下查询:
If i understand your question correctly, to answer the part to return only one direction, please try the query below:
SELECT d1.*
FROM train_schedule d1
INNER JOIN train_schedule d2 ON d2.train_no=d1.train_no
WHERE d1.stn_code = 'JU' and d2.stn_code = 'JP'
AND d1.distance < d2.distance
JU(离开)将始终具有小于JP(到达)的距离。
JU (departure) will always have distance less than JP (arrival).
这篇关于数据库查询查找特定日期两个站之间的火车,如irctc.co.in的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!