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

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和值，即使该语言中没有翻译记录。要么我丢失了一些连接技术，要么join(不涉及语言表)在这里不起作用，因为在指定的语言中不返回具有非现有翻译的属性。

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)

我用的是这样的子查询，这里的问题是用相同的参数对同一个表进行两个子查询

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和转换，因此我将concat列并在稍后从字符串中获得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).

[[[语言属性]到翻译](连接3个表似乎比子查询性能差)。

1 个解决方案

#1

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 are a 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 attribute is the first table in this join, and that this virtual table I've called b is the second table.

注意，真正的表属性是这个连接中的第一个表，而我所调用的虚拟表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 c containing two columns, joined it to the other two, used one of the columns for the ON clause, and returned the other as a column in your result set.

看看将会怎样?您已经生成了一个包含两个列的虚拟表c，并将它与另外两个列连接在一起，使用了ON子句中的一个列，并将另一个作为结果集中的一列返回。

#1