多次指定相关名称“CONVERT"

2021-09-10 00:00:00 sql tsql sql-server

在@avery_larry 的帮助下，我完成了查询，但现在面临的问题很少.我可以从另外 2 个表中添加金额.现在我必须从同一个表中添加两个不同部分的金额，没有列.第 1 列是第 1 部分，第 2 列是第 2 部分.金额栏相同.当我添加子查询时，我收到错误消息在 FROM 子句中多次指定了相关名称 CONVERT."CONVERT 是表名.这是我的代码.

With the help of @avery_larry, I done my query but now facing little problem. I am able to add amount from another 2 tables. Now I have to add amount from same table with two different part no columns. 1st col is part1 and 2nd col is part2. Amount column is same. When I add sub query, I am getting error that "The correlation name CONVERT is specified multiple times in a FROM clause." CONVERT is table name. Here is my code.

SELECT  dbo.[PART LIST].PART, 
        [COST ALL].[cost total], 
        [SELL ALL].[sell total], 
        [CONVERT].[FROM total], 
        [convert].[TO total]
FROM    
    dbo.[PART LIST] 
LEFT OUTER JOIN (SELECT PART, SUM(AMT) AS [cost total]
     FROM  dbo.[COST ALL] AS [COST ALL_1]
     WHERE (STREAM = N'Y') AND (USAGE = N'MUM')
     GROUP BY PART ) AS [COST ALL] ON [COST ALL].PART = dbo.[PART LIST].PART 
LEFT OUTER JOIN (SELECT PART, SUM(AMT) AS [sell total]
    FROM dbo.[SELL ALL] AS [SELL ALL_1]
    WHERE (STREAM = N'FSA') AND (USAGE = N'MUM')
    GROUP BY PART ) AS [SELL ALL] ON [SELL ALL].PART = dbo.[PART LIST].PART 
LEFT OUTER JOIN (SELECT [From PART], SUM(Amt) AS [FROM total]
    FROM dbo.[convert] AS CONVERT_1
    GROUP BY [From PART] ) AS [CONVERT] ON [CONVERT].[From PART] = dbo.[PART LIST].PART 
LEFT OUTER JOIN (SELECT [TO PART], SUM(Amt) AS [TO total]
    FROM dbo.[convert] AS CONVERT_1
    GROUP BY [TO PART] ) AS [CONVERT] ON [CONVERT].[TO PART] = dbo.[PART LIST].PART

目前，为了绕过错误，我制作了另一个带有差异名称的表.但是有两个具有相同数据的表并定期用新数据更新这两个表是一个问题.我宁愿解决错误并只使用一张表.

Currently, to bypass error, I have made another table with diff name. But having two tables with same data and updating both with new data regularly is a problem. I would rather resolve the error and use only one table.

请帮忙.

推荐答案

@Kryesec 是正确的.每个子查询必须有一个唯一的别名.

@Kryesec is correct. Each subquery must have a unique alias.

你不能这样做:

select *
from (
   select col1
   from table1
) AS [CONVERT]
left outer join (
   select col1
   from table2
) AS [CONVERT] on [CONVERT].col1 = [CONVERT].col1

这有 [CONVERT] 定义了两次.因为没有办法知道哪个 [CONVERT].col1我们真正想要的，这是无效的，并产生你看到的错误.
您可以多次使用 [CONVERT] 作为您选择 FROM 的表.但是，要做到这一点，每个引用必须在范围内是唯一的.这意味着您不必在每个子查询中为 [CONVERT] 设置别名，因为它在每个子查询中都是唯一的(在范围内是唯一的).但是每个子查询都必须有一个唯一的别名，这就是您的错误消息的来源.

This has [CONVERT] defined twice. Because there is no way to know which [CONVERT].col1 we would actually want, this is invalid and produces the error you see.
You CAN use [CONVERT] multiple times as the table your are selecting FROM. To do so, though, each reference must be unique in scope. Meaning you don't have to alias [CONVERT] inside each subquery because it is unique inside each subquery (unique in scope). BUT each subquery then must have a unique alias, and that is where your error message comes from.

以下是我认为您想要的代码.注意我从子查询内部删除了别名——它们是不必要的，尽管它们同样不会引起任何问题.应该解决您的错误的主要更改是将最后 2 个子查询从使用别名 [CONVERT] 更改为使用别名 [FROM_CONVERT] 和 [TO_CONVERT] 分别.

The following is what I think you want your code to be. Note I removed the aliases from inside the subqueries -- they are unnecessary, though equally they are not causing any problems. The primary change that should resolve your error is changing the final 2 subqueries from both using alias [CONVERT] to using the aliases [FROM_CONVERT] and [TO_CONVERT] respectively.

SELECT  dbo.[PART LIST].PART, 
        [COST ALL].[cost total], 
        [SELL ALL].[sell total], 
        [FROM_CONVERT].[FROM total], 
        [TO_CONVERT].[TO total]
FROM dbo.[PART LIST] 
LEFT OUTER JOIN (SELECT PART, SUM(AMT) AS [cost total]
     FROM  dbo.[COST ALL]
     WHERE (STREAM = N'Y') AND (USAGE = N'MUM')
     GROUP BY PART
) AS [COST ALL] ON [COST ALL].PART = dbo.[PART LIST].PART
LEFT OUTER JOIN (SELECT PART, SUM(AMT) AS [sell total]
    FROM dbo.[SELL ALL]
    WHERE (STREAM = N'FSA') AND (USAGE = N'MUM')
    GROUP BY PART
) AS [SELL ALL] ON [SELL ALL].PART = dbo.[PART LIST].PART
LEFT OUTER JOIN (SELECT [From PART], SUM(Amt) AS [FROM total]
    FROM dbo.[convert]
    GROUP BY [From PART]
) AS [FROM_CONVERT] ON [FROM_CONVERT].[From PART] = dbo.[PART LIST].PART 
LEFT OUTER JOIN (SELECT [TO PART], SUM(Amt) AS [TO total]
    FROM dbo.[convert]
    GROUP BY [TO PART]
) AS [TO_CONVERT] ON [TO_CONVERT].[TO PART] = dbo.[PART LIST].PART

作为旁注——如果您不在任何列名、别名或表格等中使用空格，您可能会更高兴.这可以让您避免使用引号/方括号.在您的代码中，作为一个示例，我建议 AS COST_ALL on COST_ALL.PART = 和 select ... sum(amt) as FROM_TOTAL.
此外，对表、列、数据库等的名称使用任何关键字是(非常)糟糕的设计.看起来您有一个名为 [CONVERT] 的表.也许此时您无法对设计进行任何更改，但如果可以，您应该这样做，并且您应该在未来的任何项目中记住这一点.

As a side note -- you'll probably be happier if you do not use spaces in any of your column names or aliases or tables etc. This allows you to avoid quotes/square brackets. In your code, as one example, I would suggest AS COST_ALL on COST_ALL.PART = and select ... sum(amt) as FROM_TOTAL.
Additionally, it is (very) bad design to use any keywords for names of tables, columns, databases etc. It looks like you have a table named [CONVERT]. Perhaps you cannot change anything with your design at this point, but you should if you can, and you should remember this for any future projects.

相关文章