在MySQL子查询中选择多个列/字段 [英] Selecting multiple columns/fields in MySQL subquery

问题描述

基本上有属性表和翻译表-一个属性有很多翻译.

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

即使没有该语言的翻译记录，我也需要从翻译中为指定属性的每个属性选择id和值.我缺少某种连接技术，或者连接(不涉及语言表)在这里不起作用，因为以下操作不会返回具有指定语言的不存在的翻译的属性.

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.

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

所以我正在使用这样的子查询，这里的问题是使两个具有相同参数的子查询到同一个表中(感觉像性能消耗，除非mysql对那些子查询进行分组，我对此表示怀疑，因为这会使您执行许多类似的子查询)

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;

我希望能够从一个查询中获取ID和翻译，因此我可以合并列并在以后从字符串中获取ID，这至少可以进行单个子查询，但仍然看起来不正确.

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.

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

问题部分. 有没有一种方法可以从单个子查询中获取多个列，或者我应该使用两个子查询(mysql足够聪明来对它们进行分组?)还是要加入以下方法:

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:

[[归因于语言]到翻译](连接3个表似乎比子查询的性能差).

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

推荐答案

是的，您可以执行此操作.您需要的诀窍是有两种将表从表服务器中移出的方法.一种方法是..

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

另一种方式是

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

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

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

因此，使用您的第二个代码示例(我猜您希望在此处检索的列):

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)

请注意，您的真实表attribute是此联接中的第一个表，而我称为b的虚拟表是第二个表.

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.

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

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)

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

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.

这篇关于在MySQL子查询中选择多个列/字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！