如何根据另一列的值查找列的值？

I have a simple metro line system modeled like this:

我有一个简单的地铁线路系统,模仿如下:

A line has multiple stations.

一条线有多个站。

Each station has a timetable.

每个站都有一个时间表。

A timetable contains some time info and a direction that denotes which direction of the line the time info is for.

时间表包含一些时间信息和指示时间信息所在行的方向的方向。

And my question is, given a timetable, how do I find the terminal station that belongs to the line of that direction?

我的问题是,如果有时间表,我如何找到属于该方向线的终端站?

My tables are structured like this:

我的表格结构如下:

CREATE TABLE schedule (
    id serial PRIMARY KEY,
    to_start boolean,
    start_time time,
    end_time time,
    station_id REFERENCES station
);

CREATE TABLE station (
    id serial PRIMARY KEY,
    name varchar,
    order integer,
    line_id integer REFERENCES line
);

CREATE TABLE line (
    id serial PRIMARY KEY,
    name varchar
);

If schedule.to_start is true, it means the direction points to station with an order of 1, and false to max(order).

如果schedule.to_start为true,则表示方向指向站的顺序为1,false表示最大(顺序)。

Here is some sample data:

以下是一些示例数据:

schedule table

 id | to_start | start_time | end_time | station_id 
----+----------+------------+----------+------------
  1 | f        | 06:02:00   | 22:02:00 |          2
  2 | t        | 06:35:00   | 23:07:00 |          2

station table

 id |     name     | order  | line_id 
----+--------------+--------+---------
  1 | Station A    |      1 |       1
  2 | Station B    |      2 |       1
  3 | Station C    |      3 |       1
  4 | Station D    |      1 |       2

line table

 id |  name  
----+--------
  1 | Line 1 
  2 | Line 2

So if given a schedule.id value like 1, how do I find the corresponding station.name which should be Station C (because that schedule is of station with id 2, which belongs to line with id 1 and since to_start is false, it point to the end of line, which should be Station C)?

因此,如果给定一个schedule.id值为1,我如何找到相应的station.name应该是C站(因为该时间表是id为2的站,属于id为1的行,因为to_start为false,所以指向行尾,哪个应该是C站)?

I use postgresql and my initial try relied on connecting to database multiple times, in which case I begin by selecting the schedule based on the given id, save its to_start and station_id, then select the corresponding station using the saved station_id, save its line_id, and based on the value of to_start I either select station with line_id and an order of 1, or find max(order) first and then select station with line_id and the max order.

我使用postgresql并且我的初始尝试依赖多次连接到数据库,在这种情况下我首先根据给定的id选择计划,保存其to_start和station_id,然后使用保存的station_id选择相应的站点,保存其line_id,并且基于to_start的值,我选择带有line_id和1的顺序的工作站,或先找到max(order),然后选择带有line_id和最大顺序的工作站。

It feels really cumbersome and not letting postgresql make the best optimization possible.

感觉非常麻烦,并且不让postgresql做出最佳的优化。

Is there any way to do this with one sql?

有没有办法用一个sql做到这一点?

3 个解决方案

#1

This will do:

这样做:

select   ds.*
from     schedule s
join     station  qs on qs.id = s.station_id
join     station  ds using (line_id)
where    s.id = 1
order by ds."order" * case when s.to_start then 1 else -1 end
limit    1

Notes:

order is reserved keyword, so it is unwise to use it as a column name

order是保留关键字,因此将它用作列名是不明智的

your schema looks a little odd to me, f.ex. you'll need to duplicate stations, if multiple lines are crossing it

你的架构对我来说有点奇怪,f.ex。如果多条线穿过它,你需要复制电台

http://rextester.com/OITNB38639

#2

You can do this with joins. You can do mutiple joins in one query.

你可以用连接来做到这一点。您可以在一个查询中执行多个连接。

SELECT * FROM schedule JOIN station on station.id = schedule.station_id JOIN line line.id = schedule.line_id WHERE sechdule.id = '1'

more info about joins: https://www.w3schools.com/sql/sql_join.asp

有关联接的更多信息:https://www.w3schools.com/sql/sql_join.asp

#3

A couple of CTEs, a new row_number() and some lovely self joins

一些CTE,一个新的row_number()和一些可爱的自联接

with FirstLast as
(
select a1.*, 
       row_number() over(partition by line_id order by a1.order desc) as order2
from Station a1
)
, Stage2 as
(
select a1.id, a1.name, a2.name as FirstStation, a3.name as LastStation
from FirstLast a1
inner join FirstLast a2
  on a1.line_id = a2.line_id
  and a2.order = 1
inner join FirstLast a3
  on a1.line_id = a3.line_id
  and a3.order2 = 1
)
select x.id, 
       x.name, 
       case 
         when z.to_start = 't' then x.FirstStation 
         else x.LastStation 
       end as TheStation
from schedule z
inner join Stage2 x
  on z.station_id = x.id

#1