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'.

我是 SQL 新手。

如前所述，问题是您从两个表中选择 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;

除此之外，正如 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

但是，求求您尽可能避免使用 SELECT *。当您进行交互式查询以节省打字时，它可能会起作用，但在生产代码中切勿使用它。