是的,你可以这样做。您需要的诀窍是有两种方法可以从表服务器中获取表。一种方法是......
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)请注意,您的真实表attribute是此连接中的第一个表,并且我称为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子句中的一列,并将另一列作为结果集中的列返回。