問題描述

在@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.

這篇關于多次指定相關名稱“CONVERT"的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！