sql 2005 - 该列被多次指定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1058606/
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
sql 2005 - The column was specified multiple times
提问by Vinko Vrsalovic
I am getting the following error when trying to run this query in sql 2005:
尝试在 sql 2005 中运行此查询时出现以下错误：
SELECT tb.*
FROM (
SELECT *
FROM vCodesWithPEs INNER JOIN vDeriveAvailabilityFromPE
ON vCodesWithPEs.PROD_PERM = vDeriveAvailabilityFromPE.PEID
INNER JOIN PE_PDP ON vCodesWithPEs.PROD_PERM = PE_PDP.PEID
) AS tb;
Error: The column 'PEID' was specified multiple times for 'tb'.
I am new to sql. Thank You in advance for your advise.
我是 sql 的新手。预先感谢您的建议。
Eneo.
埃内奥。
回答by Vinko Vrsalovic
The problem, as mentioned, is that you are selecting PEID from two tables, the solution is to specify which PEID do you want, for example
如前所述，问题是您要从两个表中选择 PEID，解决方案是指定您想要哪个 PEID，例如
SELECT tb.*
FROM (
SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
ON tb1.PROD_PERM = tb2.PEID
INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID
) AS tb;
That aside, as Chris Lively cleverly points out in a comment the outer SELECT is totally superfluous. The following is totally equivalent to the first.
除此之外，正如 Chris Lively 在评论中巧妙地指出的那样，外部 SELECT 完全是多余的。以下完全等同于第一个。
SELECT tb1.PEID,tb2.col1,tb2.col2,tb3.col3 --, and so on
FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
ON tb1.PROD_PERM = tb2.PEID
INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID
or even
甚至
SELECT *
FROM vCodesWithPEs as tb1 INNER JOIN vDeriveAvailabilityFromPE as tb2
ON tb1.PROD_PERM = tb2.PEID
INNER JOIN PE_PDP tb3 ON tb1.PROD_PERM = tb3.PEID
but please avoid using SELECT * whenever possible. It may work while you are doing interactive queries to save typing, but in production code never use it.
但请尽可能避免使用 SELECT *。它可能在您进行交互式查询以节省输入时起作用，但在生产代码中永远不要使用它。
回答by MicSim
Looks like you have the column PEIDin both tables: vDeriveAvailabilityFromPEand PE_PDP. The SELECTstatement tries to select both, and gives an error about duplicate column name.
看起来您在两个表中都有列PEID：vDeriveAvailabilityFromPE和PE_PDP。该SELECT语句尝试同时选择两者，并给出关于重复列名的错误。
回答by Jeremy Smyth
You're joining three tables, and looking at all columns in the output (*).
您正在连接三个表，并查看输出 (*) 中的所有列。
It looks like the tables have a common column name PEID, which you're going to have to alias as something else.
看起来这些表有一个公共列名 PEID，您必须将其别名为其他名称。
Solution: don't use * in the subquery, but explicitly select each column you wish to see, aliasing any column name that appears more than once.
解决方案：不要在子查询中使用 *，而是明确选择您希望查看的每一列，为出现多次的任何列名设置别名。
回答by NotMe
Instead of using * to identify collecting all of the fields, rewrite your query to explicitly name the columns you want. That way there will be no confusion.
不要使用 * 来标识收集所有字段，而是重写查询以明确命名所需的列。这样就不会有混乱了。
回答by Rubeshkumar
just give new alias name for the column that repeats,it worked for me.....
只需为重复的列提供新的别名，它对我有用.....
