英文:
What is that Teradata? syntax for a subquery aliased with a parameter (can't find a name of ref for this)
问题
以下是已翻译的代码部分：
SELECT LOOKUP.LOOKUP_ID
FROM (
SELECT INNER_ID FROM
(
SELECT max(INNER_ID) INNER_ID
FROM INNER_TABLE
) INNER_QUERY
) LOOKUP (LOOKUP_ID)
这是一段遗留代码 - 我继承的 - 在Teradata中，仍然在TD 16.20上运行，但这是我遇到的唯一一个如此奇怪的情况。为什么要像这样使用它 - 是另一个问题。
英文:
it goes like this:
SELECT LOOKUP.LOOKUP_ID
FROM (
SELECT INNER_ID FROM
(
SELECT max(INNER_ID) INNER_ID
FROM INNER_TABLE
) INNER_QUERY
) LOOKUP (LOOKUP_ID)
It's a legacy code - I inherited - in Teradata, still runs in TD 16.20, but this is the only instance I have run into something so strange. And why would you key it like this - is another question.
答案1
得分: 0
这些是FROM子句中的子查询。这种子查询也被称为"派生表"。
最内层的子查询是
(
SELECT MAX(inner_id) inner_id
FROM inner_table
) inner_query
在这里，我们选择最大的inner_id，然后再次将这个值称为inner_id。我们通过别名inner_query引用这个单行查询结果。
下一个子查询是
(
SELECT inner_id
FROM ( <上述子查询> ) inner_query
) lookup (lookup_id)
我们选择前面提到的仍然称为inner_id的最大inner_id，并将这个单行结果集现在称为lookup，其中的值为lookup_id。
最后，我们选择那个值
SELECT lookup.lookup_id FROM ( ... ) lookup (lookup_id);
当然，我们也可以只用下面的查询来获得相同的结果
SELECT MAX(inner_id) AS lookup_id
FROM inner_table;
也许你只是不熟悉表和列的别名语法：
FROM mytable table_alias
或
FROM mytable table_alias (col1_alias, col2_alias, ...)
英文:
Well these are subqueries in the FROM clause. Such subqueries are also known as "derived tables".
The innermost subquery is
(
SELECT MAX(inner_id) inner_id
FROM inner_table
) inner_query
Here we select the maximum inner_id and call this value inner_id again. We reference this one-row query result by the alias inner_query.
The next subquery is
(
SELECT inner_id
FROM ( <above subquery> ) inner_query
) lookup (lookup_id)
We select the aforementioned maximum inner_id that is still called inner_id and call the one-row result set now lookup and the value lookup_id.
At last we select that value
SELECT lookup.lookup_id FROM ( ... ) lookup (lookup_id);
We would get the same result with a mere
SELECT MAX(inner_id) AS lookup_id
FROM inner_table;
of course.
Maybe you are just unfamiliar with the alias name syntax for tables and columns:
FROM mytable table_alias
or
FROM mytable table_alias (col1_alias, col2_alias, ...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论