是的,你可以这样做。你需要的诀窍是这样的概念,有两种方式从表服务器获取表。一种方法是..
FROM TABLE A
另一种方式是
FROM (SELECT col as name1, col2 as name 2 FROM ...) B
注意,select子句和它周围的括号是一个表,一个虚拟表。
所以,使用你的第二个代码示例(我猜在你希望在这里检索的列):
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)
请注意,您的实际表属性是此连接中的第一个表,并且该虚拟表我称为b是第二个表。
当虚拟表是某种类型的汇总表时,此技术特别方便。例如
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子句,并将另一列作为结果集中的列返回。