每个派生表都必须有自己的别名 - 什么时候是派生表? [英] Every derived table must have its own alias - when is something a derived table?

问题描述

首先，让我注意，我知道有类似问题的其他线程，但它们对我的理解没有太大帮助.相反，我现在有时会遇到分配别名会破坏我的代码的问题，如下所述.

First, let me note that I am aware of the other threads with a similar question, but they didn't help my understanding very much. On the opposite, I now sometimes run into the problem that assigning aliases ruins my code, as described below.

所以我经常收到这样的错误消息，然后我开始为那些我认为是派生表"的子查询提供别名，但有时这样做时，我现在收到消息你的 SQL 有错误语法'代替，并在删除 'AS ...' 语句后，一切运行正常.所以我真的想弄清楚什么时候是派生表，因此需要和别名，什么时候不是.我会给你一个例子:给定一些表 P、LTP 和 T，下面的查询运行完美:

So I got said error message very often, and in turn started to give aliases to those subqueries which I thought were 'derived tables', but sometimes when doing so, I now get the message 'You have an error in your SQL syntax' instead, and after removing the 'AS ...' statement, everything runs fine. So I am really trying to figure out when exactly something is a derived table and hence needs and alias, and when not. I will give you an example: Given some tables P, LTP, and T, the following query runs flawless:

SELECT DISTINCT pname FROM P WHERE P.pnr IN (SELECT pnr FROM LTP WHERE lnr='L1' AND tnr IN (SELECT tnr FROM T WHERE gewicht>10));

括号中的语句如何不是派生表?我会假设在这种情况下我不得不给他们这样的别名:

How are the statements in the brackets not derived tables though? I would have assumed that in this case I would have had to give them aliases like this:

SELECT DISTINCT pname FROM P WHERE P.pnr IN (SELECT pnr FROM LTP WHERE lnr='L1' AND tnr IN (SELECT tnr FROM T WHERE gewicht>10) AS TNEW) AS LTPNEW;

但这两种方式都会破坏代码.如果有人能指出我的误解，我将不胜感激.

but both of these ruin the code. I would really appreciate if somebody could point out to me what exactly I am misunderstanding.

推荐答案

如果子查询在 table_references 查询的部分(FROM 子句和所有伴随的 JOINs)，它需要包括别名.

If the subquery is in the table_references portion of a query (the FROM clause and all accompanying JOINs), it needs to include an alias.

如果子查询出现在其他地方，比如在 WHERE 或 SELECT 部分，它只是一个常规子查询，不需要别名.

If the subquery appears elsewhere, like in the WHERE or SELECT section, it's just a regular subquery and no aliasing is required.

来自文档:

派生表是FROM 子句中子查询的内部名称.

Derived tables is the internal name for subqueries in the FROM clause.

根据经验，如果您可以通过名称引用子查询中的列，则子查询需要一个别名以防止歧义.

As a rule of thumb, if you can reference a column from the subquery by name, then the subquery needs an alias to prevent ambiguity.

这篇关于每个派生表都必须有自己的别名 - 什么时候是派生表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！