SQL 插入语句中表名的别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9099906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alias for table name in SQL insert statement
提问by fortran
Is it possible to specify an alias name for the table I am inserting values into?
是否可以为要插入值的表指定别名？
I want to specify a condition inside a nested query and the table is too verbose...
我想在嵌套查询中指定一个条件并且该表太冗长...
Something like turning this:
像这样转动：
INSERT INTO my_table_with_a_very_long_name (col_a, col_b, col_c)
SELECT foo, bar, baz
FROM other_table
WHERE
other_table.some_value >
(SELECT max(other_value) FROM my_table_with_a_very_long_name);
into this:
进入这个：
INSERT INTO my_table_with_a_very_long_name AS t (col_a, col_b, col_c)
SELECT foo, bar, baz
FROM other_table
WHERE
other_table.some_value > (SELECT max(other_value) FROM t);
(obviously my case is longer and involves a few references more)
（显然我的案例更长，涉及更多参考资料）
采纳答案by MatBailie
You don't alias a table, you alias an instance of a table reference.
您不为表设置别名，而是为表引用的实例设置别名。
This allows self joins, etc as you have mutliple instances of references to the same physical table. It's not a case where each ASgives that table a new name elsewhere, it's just an alias to refer to That particular reference.
这允许自连接等，因为您有多个引用同一个物理表的实例。并不是每个人都AS在其他地方为该表赋予新名称，这只是引用该特定引用的别名。
In your case, there are two show stoppers...
在您的情况下，有两个节目阻止...
The table being inserted into isn't itself part of the select query, it's not a referenced set in the same way as foo, baror bazfor example. So, you can't alias it at all (because there's no need, it can never be referenced).
被插入的表本身不是选择查询的一部分，它不是与foo,bar或baz例如相同方式的引用集。所以，你根本不能给它取别名（因为没有必要，它永远不能被引用）。
Also, even if it was, you can't reference the whole table through an alias. You reference a field, as part the query itterating through the set. For example, this doesn't work either...
此外，即使是这样，您也无法通过别名引用整个表。您引用一个字段，作为遍历集合的查询的一部分。例如，这也不起作用......
SELECT * FROM myTable AS xxx WHERE id = (SELECT MAX(id) FROM xxx)
You can get around the latter example using...
您可以使用...绕过后一个示例
WITH xxx AS (SELECT * FROM myTable)
SELECT * FROM xx WHERE id = (SELECT MAX(id) FROM xxx)
But that still brings us back to the first point, the table being inserted into never gets referenced in the query part of your statement.
但这仍然让我们回到第一点，被插入的表永远不会在语句的查询部分中被引用。
The only way I can think of getting close is to create a view...
我能想到的唯一方法就是创建一个视图......
回答by John Woo
I think the answer is NO. There is no ASafter the tableName
我认为答案是否定的。AS之后没有tableName
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Update
更新
The ASclause became part of PostgreSQL as of version 9.5, though as @MatBailie notes above, the nesting means you'll need to alias the INSERTquery and the SELECTsub-query separately or things will break. e.g.:
从版本 9.5 开始，该AS子句成为 PostgreSQL 的一部分，但正如上面@MatBailie 指出的那样，嵌套意味着您需要分别为查询和子查询设置别名，否则事情会中断。例如：INSERTSELECT
> CREATE TABLE foo (id int, name text);
CREATE TABLE
> INSERT INTO foo VALUES (1, 'alice'), (2, 'bob'), (3, 'claire');
INSERT 0 3
> INSERT INTO foo AS f (SELECT f.* from f);
ERROR: relation "f" does not exist
LINE 1: INSERT INTO foo AS f (SELECT f.* from f);
^
-- Next line works, but is confusing. Pick distinct aliases in real life.
-- I chose the same 'f' to illustrate that the sub-select
-- really is separate.
> INSERT INTO foo AS f (SELECT f.* from foo f);
INSERT 0 3
> > SELECT * FROM foo;
id | name
----+--------
1 | alice
2 | bob
3 | claire
1 | alice
2 | bob
3 | claire
(6 rows)
回答by RPh_Coder
As others have said, you cannot alias the name as part of the INSERT INTOstatement. You would need to put it in the subquery in the WHEREstatement.
正如其他人所说，您不能将名称作为INSERT INTO语句的一部分使用别名。您需要将它放在WHERE语句的子查询中。
INSERT INTO my_table_with_a_very_long_name (col_a, col_b, col_c)
SELECT foo, bar, baz
FROM other_table
WHERE
other_table.some_value > (SELECT max(other_value) FROM
my_table_with_a_very_long_name AS t);
