如何根据具有相同`id`的另一行有效地选择列的字段? [英] How to efficiently SELECT a column's field based on another row with the same `id`?

问题描述

这是我的桌子:

Here is my table:

 id | title    | lang
----+----------+------
 1  | Moscow   | en
 1  | Москва   | ru
 2  | Helsinki | en 
 2  | Хельсинки| ru

我想通过en标题有效地获得ru标题.

I would like to efficiently get the ru title by en title.

此刻，我首先获得条目的id，然后手动进行另一个查询.

At the moment, I get the id of the entry first and then make another query by hand.

还有其他更优雅的解决方案吗?

Any other, more elegant, solutions?

推荐答案

一个加入与子查询，

A SELF JOIN might be of help and is usually a preferable solution to lots of nested queries, if not for performance reasons (see: Join vs. sub-query, Rewriting Subqueries as Joins) certainly for readability.

在您的情况下，请尝试:

In your case try:

SELECT movies_ru.title 
 FROM movies AS movies_ru 
 JOIN movies AS movies_en 
 ON movies_ru.id = movies_en.id
 WHERE movies_ru.lang = "ru"
 AND movies_en.lang = "en"
 AND movies_en.title = "The English Title"

这篇关于如何根据具有相同`id`的另一行有效地选择列的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！