在 MySQL 子查询中选择多个列/字段

Selecting multiple columns/fields in MySQL subquery

提问by Martin

Basically there is attribute table and translation table - many translations for one attribute.

基本上有属性表和翻译表——一个属性有很多翻译。

I need to select id and value from translation for each attribute in specified language, even if there is no translation record in that language. Either i am missing some join technique or join (without involving language table) is not working here since following do not return attributes with non existing translations in specified language.

我需要为指定语言的每个属性从翻译中选择 id 和值，即使该语言没有翻译记录。要么我缺少一些连接技术，要么连接（不涉及语言表）在这里不起作用，因为以下不会返回具有指定语言不存在翻译的属性。

select a.attribute, at.id, at.translation 
from attribute a left join attributeTranslation at on a.id=at.attribute
where al.language=1;

So i am using subqueries like this, problem here is making two subqueries to the same table with same parameters (feels like performance drain unless mysql groups those, which i doubt since it makes you do many similar subqueries)

所以我正在使用这样的子查询，这里的问题是使用相同的参数对同一个表创建两个子查询（感觉就像性能下降，除非 mysql 对这些进行分组，我对此表示怀疑，因为它使您执行许多类似的子查询）

select attribute, 
(select id from attributeTranslation where attribute=a.id and language=1),
(select translation from attributeTranslation where attribute=a.id and language=1), 
from attribute a;

I would like to be able to get id and translation from one query, so i concat columns and get the id from string later, which is at least making single subquery, but still not looking right.

我希望能够从一个查询中获取 id 和翻译，所以我连接列并稍后从字符串中获取 id，这至少是创建单个子查询，但看起来仍然不正确。

select attribute,
(select concat(id,';',title)
    from offerAttribute_language 
    where offerAttribute=a.id and _language=1
)
from offerAttribute a

So the question part. Is there a way to get multiple columns from a single subquery or should i use two subqueries (mysql is smart enough to group them?) or is joining the following way to go:

所以问题部分。有没有办法从单个子查询中获取多个列，或者我应该使用两个子查询（mysql 足够聪明来将它们分组？）还是加入以下方式：

[[attribute to language] to translation] (joining 3 tables seems like worse performance than subquery).

[[attribute to language] to translation]（加入 3 个表似乎比子查询的性能更差）。

回答by O. Jones

Yes, you can do this. The knack you need is the concept that there are two ways of getting tables out of the table server. One way is ..

是的，你可以这样做。您需要的诀窍是有两种方法可以从表服务器中获取表。一种方法是..

FROM TABLE A

The other way is

另一种方式是

FROM (SELECT col as name1, col2 as name 2 FROM ...) B

Notice that the select clause and the parentheses around it area table, a virtual table.

请注意，select 子句及其周围的括号是一个表，一个虚拟表。

So, using your second code example (I am guessing at the columns you are hoping to retrieve here):

因此，使用您的第二个代码示例（我猜测您希望在此处检索的列）：

SELECT a.attr, b.id, b.trans, b.lang
FROM attribute a
JOIN (
 SELECT at.id AS id, at.translation AS trans, at.language AS lang, a.attribute
 FROM attributeTranslation at
) b ON (a.id = b.attribute AND b.lang = 1)

Notice that your real table attributeis the first table in this join, and that this virtual table I've called bis the second table.

请注意，您的真实表attribute是此连接中的第一个表，而我调用的这个虚拟表b是第二个表。

This technique comes in especially handy when the virtual table is a summary table of some kind. e.g.

当虚拟表是某种类型的汇总表时，这种技术特别方便。例如

SELECT a.attr, b.id, b.trans, b.lang, c.langcount
FROM attribute a
JOIN (
 SELECT at.id AS id, at.translation AS trans, at.language AS lang, at.attribute
 FROM attributeTranslation at
) b ON (a.id = b.attribute AND b.lang = 1)
JOIN (
 SELECT count(*) AS langcount,  at.attribute
 FROM attributeTranslation at
 GROUP BY at.attribute
) c ON (a.id = c.attribute)

See how that goes? You've generated a virtual table ccontaining two columns, joined it to the other two, used one of the columns for the ONclause, and returned the other as a column in your result set.

看看情况如何？您已经生成了一个c包含两列的虚拟表，将其连接到另外两列，将其中一列用于ON子句，并将另一列作为结果集中的列返回。