根据当天明天(巴士班次)多重排序

我被困在严重的问题,我将用我下面的查询说。 在这里, j5代表周五和j6代表星期六(1至7 ...周日至周一)。

如你所知,公交车都因星期的时间不同的时间表。 在这里,我服用后,未来5车次发车25:00:00cal (j5)和/或之后01:00:00cal2 (j6) 公交站牌正在建造这样的:

如果是凌晨1点,那么当前公交车时间是25日,凌晨2点是26 ...你得到它。 所以,如果我想今天出发的行程,让后说,凌晨1点,我可能只能得到2-3,因为“公交车”的日子即将结束。 为了解决这个问题,我想从第二天添加下一个出发(这里是周五周六后)。 但是第二天开始00一样,每天在我们的世界。

因此,我想要做的是:获取所有的星期五下次旅行j5 25:00:00之后。 如果我没有做5,然后让所有n行出发周六之后01:00:00(自25:00:00 = 01:00:00)。

例如:我得到的出发行程为25:16:00,25:46:00和26:16:00星期五。 这是3.我想要再获得第二天2等出发的行程,所以我在最后得到5,这将是这个样子4时50分00秒和5点15分00秒。 因此,根据X停止下一个出发的行程是:25:16:00(星期五)25:46:00(星期五)26:16:00(星期五),四时50分○○秒(星期六),5时15分零零秒(星期六)。

我有问题,从两个结果进行排序trips.trip_departure

我知道这可能是复杂的,它的复杂,我来解释,但是...无论如何。 有问题,我在这里。 非常感谢提前!

PS:使用MySQL 49年5月1日和PHP 5.3.8 PS2:我想避免在PHP进行多次查询,所以我想这样做在一个查询中,不管是什么。

SELECT trips.trip_departure, trips.trip_arrival, trips.trip_total_time, trips.trip_direction FROM trips, trips_assoc, ( SELECT calendar_regular.cal_regular_id FROM calendar_regular WHERE calendar_regular.j5 = 1 ) as cal, ( SELECT calendar_regular.cal_regular_id FROM calendar_regular WHERE calendar_regular.j6 = 1 ) as cal2 WHERE trips.trip_id = trips_assoc.trip_id AND trips.route_id IN (109) AND trips.trip_direction IN (0) AND trips.trip_period_start <= "2011-11-25" AND trips.trip_period_end >= "2011-11-25" AND ( ( cal.cal_regular_id = trips_assoc.calendar_id AND trips.trip_departure >= "25:00:00" ) OR ( cal2.cal_regular_id = trips_assoc.calendar_id AND trips.trip_departure >= "01:00:00" ) ) ORDER BY trips.trip_departure ASC LIMIT 5

编辑表结构:

表calendar_regular

J1平均周日,周一J7等)。

`cal_regular_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `j1` tinyint(1) NOT NULL COMMENT 'Lundi', `j2` tinyint(1) NOT NULL COMMENT 'Mardi', `j3` tinyint(1) NOT NULL COMMENT 'Mercredi', `j4` tinyint(1) NOT NULL COMMENT 'Jeudi', `j5` tinyint(1) NOT NULL COMMENT 'Vendredi', `j6` tinyint(1) NOT NULL COMMENT 'Samedi', `j7` tinyint(1) NOT NULL COMMENT 'Dimanche', PRIMARY KEY (`cal_regular_id`), KEY `j1` (`j1`), KEY `j2` (`j2`), KEY `j3` (`j3`), KEY `j4` (`j4`), KEY `j5` (`j5`), KEY `j6` (`j6`), KEY `j7` (`j7`)

数据:

cal_regular_id j1 j2 j3 j4 j5 j6 j7 1 0 0 0 0 1 0 0 2 0 0 0 1 1 0 0 3 1 1 1 1 1 0 0 4 0 0 0 0 0 1 0 5 0 0 0 0 0 0 1

有些公交车是缴费x天它的定义时,在周...分配给trip_assoc表的表。

行程表

`agency_id` smallint(5) unsigned NOT NULL, `trip_id` binary(16) NOT NULL, `trip_period_start` date NOT NULL, `trip_period_end` date NOT NULL, `trip_direction` tinyint(1) unsigned NOT NULL, `trip_departure` time NOT NULL, `trip_arrival` time NOT NULL, `trip_total_time` mediumint(8) NOT NULL, `trip_terminus` mediumint(8) NOT NULL, `route_id` mediumint(8) NOT NULL, `shape_id` binary(16) NOT NULL, `block` binary(16) DEFAULT NULL, KEY `testing` (`route_id`,`trip_direction`), KEY `trip_departure` (`trip_departure`)

trips_assoc表

`agency_id` tinyint(4) NOT NULL, `trip_id` binary(16) NOT NULL, `calendar_id` smallint(6) NOT NULL, KEY `agency_id` (`agency_id`), KEY `trip_id` (`trip_id`,`calendar_id`)

--------------解决方案-------------

首先, 永远不要让外部实体规定一个非唯一的联接列。 他们都不可能(有授权/认证)决定唯一的人(如确定GUID值)。 否则,他们得到的地方支配自然键,你的数据库自动分配行ID加盟。 另外,除非你有连接(多个几十个)的一个庞大的数字处理过没有索引行,其性能将是远远低于它在其他地方处理的头痛的一个因素。

因此,从事物的样子,你是从多个公司存储巴士时刻表(有点像谷歌必须做获取公交路线,是的)。
下面是我会怎么处理这个:

  • 你会需要一个日历文件。 这是所有的业务场景是有用的,但将是非常有用的位置(注意:不要把任何路由相关的信息的话)。
  • 修改您的agency表来控制连接键。 机构没有得到指定它们的ID,只有他们的姓名(或一些类似的标识符)。 像下面这样的东西应该足够了:

    agency
    =============
    id - identity, incrementing
    name - Externally specified name, unique
  • 修改你route表来控制连接键。 机构应该只能够指定他们(可能非唯一) 自然键 ,所以我们需要一个代理键联接为:

    route
    ==============
    id - identity, incrementing
    agency_id - fk reference to agency.id
    route_identifier - natural key specified by agency, potentially non-unique.
    - required unique per agency_id, however (or include variation for unique)
    route_variation - some agencies use the same routes for both directions, but they're still different.
    route_status_id - fk reference to route_status.id (potential attribute, debatable)

    请注意,路由表不 ​​应实际列出的路线上浑身解数-它的唯一目的是控制哪个机构有哪些路由。

  • 创建一个locationaddress表。 这将有利于你主要是在事实,大多数转运公司倾向于通过相同的位置放多条路:

    location
    =============
    id - identity, incrementing
    address - there are multiple ways to represent addresses in a database.
    - if nothing else, seperating the fields should suffice
    lat/long - please store these properly, not as a single column.
    - two floats/doubles will suffice, although there are some dedicated solutions.
  • 在这一点上,你必须处理的路线上站两个选项:

    1. 定义一个stop表,列出所有站。 事情是这样的:

      stop
      ================
      id - identity, incrementing
      route_id - fk reference to route.id
      location_id - fk reference to location.id
      departure - Timestamp (date and time) when the route leaves the stop.

      这当然变大非常快,但使处理假日时间表容易。

    2. 定义一个schedule表设置,以及schedule_override表集:

      schedule
      ===================
      id - identity, incrementing
      route_id - fk reference to route.id
      start_date - date schedule goes into effect.

      schedule_stop
      ===================
      schedule_id - fk reference to schedule.id
      location_id - fk reference to location.id
      departure - Time (time only) when the route leaves the stop
      dayOfWeek - equivalent to whatever is in calendar.nameOfDay
      - This does not have to be an id, so long as they match

      schedule_override
      ===================
      id - identity, incrementing
      route_id - fk reference to route.id
      effective_date - date override is in effect. Should be listed in the calendar file.
      reason_id - why there's an override in effect.

      schedule_override_stop
      ===========================
      schedule_override_id - fk reference to schedule_override.id
      location_id - fk reference to location.id
      departure - time (time only) when the route leaves the stop

有了这些信息,现在我可以得到我需要的信息:

SELECT
FROM agency as a
JOIN route as b
ON b.agency_id = a.id
AND b.route_identifier = :(whatever 109 equates to)
AND b.route_variation = :(whatever 0 equates to)
JOIN (SELECT COALESCE(d.route_id, j.route_id) as route_id,
COALESCE(e.location_id, j.location_id) as location_id,
COALESCE(TIMESTAMP(c.date, e.departure),
TIMESTAMP(c.date, j.departure)) as departure_timestamp
FROM calendar as c
LEFT JOIN (schedule_override as d
JOIN schedule_override_stop as e
ON e.schedule_override_id = d.id)
ON d.effective_date = c.date
LEFT JOIN (SELECT f.route_id, f.start_date
g.dayOfWeek, g.departure, g.location_id,
(SELECT MIN(h.start_date)
FROM schedule as h
WHERE h.route_id = f.route_id
AND h.start_date > f.start_date) as end_date
FROM schedule as f
JOIN schedule_stop as g
ON g.schedule_id = f.id) as j
ON j.start_date <= c.date
AND j.end_date > c.date
AND j.dayOfWeek = c.dayOfWeek
WHERE c.date >= :startDate
AND c.date < :endDate) as k
ON k.route_id = b.id
AND k.departure_timestamp >= :leaveAfter
JOIN location as m
ON m.id = k.location_id
AND m.(location inforation) = :(input location information)
ORDER BY k.departure_timestamp ASC
LIMIT 5

这会给从指定的位置离开时,对于给定的路线,之间的所有偏离的列表startDateendDate (不含),和后leaveAfter时间戳。 声明(等效)在DB2上运行。 它拿起更改日程,将覆盖节假日等

我想的X零的建议是最好的解决办法,但我有空闲时间:)下面请看,我已经使用CONCAT处理的时间戳和下令这两个列之后。 我写了写意可以的错误,我已经使用存在,冥冥中我读了更多的速度比加入,但你可以只使用查询CONCAT并订购零部件

SELECT
trips.trip_departure,
trips.trip_arrival,
trips.trip_total_time,
trips.trip_direction,
CONCAT(trips.trip_period_start,' ',trips.trip_departure) as start,
CONCAT(trips.trip_period_end,' ',trips.trip_departure) as end,
FROM trips
WHERE EXISTS
(
SELECT
trips_assoc.calendar_id
FROM
trips_assoc
WHERE
trips.trip_id = trips_assoc.trip_id
AND EXISTS
(
SELECT
calendar_regular.cal_regular_id
FROM
calendar_regular
WHERE
cal2.cal_regular_id = trips_assoc.calendar_id
AND
(
calendar_regular.j5 = 1
OR
calendar_regular.j6 = 1
)
)
)
AND
trips.route_id IN (109)
AND
trips.trip_direction IN (0)
AND
trips.trip_period_start <= "2011-11-25"
AND
trips.trip_period_end >= "2011-11-25"
AND
(
trips.trip_departure >= "25:00:00"
OR
trips.trip_departure >= "01:00:00"
)
ORDER BY
TIMESTAMP(start) ASC,TIMESTAMP(end) ASC
LIMIT
5

编辑:复制/粘贴问题修正

分类:MySQL的 时间:2015-03-14 人气:0
本文关键词: 数据库MySQL,SQL
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.850 (s). 10 q(s)