MYSQL AND查询满足同一列 [英] MYSQL AND query to satisfy on same column

问题描述

项目目标:

我们正在开发公交时刻表Api，用户可以在其中搜索公交车.

We are developing bus timing Api where user will search for buses.

以下是我的table structure

我关注了tables

公共汽车

id | bus_name

表说明:存储所有公交车名称

路线

id | route_name

表格说明:存储所有城市名称

停止

id | stop_name

表格说明:所有停靠站名称

停止订单

id | route_id | stop_id | stop_order

表说明:在这里，我将为城市分配停靠点，并在stop_order列帮助下确定彼此相邻的停靠点

Description of table: here i will assign stops for city and stop_order column help to identify which stop next to each other

bus_timing

id | stop_order_id | bus_id | bus_timing | trip | trip_direction

表说明:在这里，我将为公交车站以及时间，行程和方向分配公交车

Description of table: Here i will assign buses for route stops along with time and trip and direction

预期输出:

1. 当用户随时间在源到目的地之间进行搜索时，Api必须随时间返回所有总线列表

1. When user search between source to destination with time then Api must return all buses list with time

如果没有直接总线，则互连的总线应显示

if direct buses not there then interconnected buses should show

例如，如果用户在stop_8至stop_18与01:00:00至12:00:00之间进行搜索，则应显示所有随时间变化的公交车列表.如果直达公交车不在两站之间行驶，则应显示互连的链接公交车列表

For example if user search between stop_8 to stop_18 with 01:00:00 to 12:00:00 then all buses list with time should show.if direct buses not there to travel between two stops then interconnected link buses list should show

输出我得到的是

PHP根据条件比较关联数组

当前的返回结果问题是

1. 即使公共汽车仅前往stop_8而不是stop_18，它也将返回所有公共汽车.但是我的结果必须仅返回将在两个站点之间行驶的那些公共汽车，这意味着它必须位于两个站点之间.

1. It will return all buses even though if bus is only travel to stop_8 but not stop_18.But my result must return only those buses which will travel between two stops i mean it must fall between both stops .

我什至不知道如何找到互连的总线列表

Even i have no idea how to find interconnected buses list

当时间段较长时，同一公交车可能会多次行驶(行程和方向)

When time range is long then there is chance of same bus will travel(trip and direction) multiple times

更新 仍在寻找答案.现在给出的答案有一些要点，因此悬赏金

Updates Still looking for answer .Right now given answer has some points so offered bounty

推荐答案

因为stop_id不能在同一行中是两个不同的值.

Because stop_id cannot be two different values in the same row.

聚合是您想要做的一种方法:

Aggregation is one way to do what you want:

SELECT b.bus_name
FROM buses b JOIN
     route_connect rc
     ON rc.busid = b.id JOIN
     stops s
     ON s.id = rc.stop_id
GROUP BY b.bus_name
HAVING SUM( s.stop_name = 'Sydney' ) > 0 AND
       SUM( s.stop_name = 'Melbourne' ) > 0;

这将返回以两个城市的名称作为站点的公交车.

This returns buses that have stops with the name of both cities.

鉴于公共汽车可以停很多站，这样做可能会更高效:

Given that buses can have lots of stops, it might be more efficient to do:

SELECT b.bus_name
FROM buses b JOIN
     route_connect rc
     ON rc.busid = b.id JOIN
     stops s
     ON s.id = rc.stop_id
WHERE s.stop_name in ('Sydney', 'Melbourne')
GROUP BY b.bus_name
HAVING COUNT(DISTINCT s.stop_name) = 2;

这篇关于MYSQL AND查询满足同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！