問題描述

在@avery_larry 的幫助下，我完成了查詢，但現(xiàn)在面臨的問題很少.我可以從另外 2 個表中添加金額.現(xiàn)在我必須從同一個表中添加兩個不同部分的金額，沒有列.第 1 列是第 1 部分，第 2 列是第 2 部分.金額欄相同.當我添加子查詢時，我收到錯誤消息在 FROM 子句中多次指定了相關(guān)名稱 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

目前，為了繞過錯誤，我制作了另一個帶有差異名稱的表.但是有兩個具有相同數(shù)據(jù)的表并定期用新數(shù)據(jù)更新這兩個表是一個問題.我寧愿解決錯誤并只使用一張表.

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我們真正想要的，這是無效的，并產(chǎn)生你看到的錯誤.
您可以多次使用 [CONVERT] 作為您選擇 FROM 的表.但是，要做到這一點，每個引用必須在范圍內(nèi)是唯一的.這意味著您不必在每個子查詢中為 [CONVERT] 設置別名，因為它在每個子查詢中都是唯一的(在范圍內(nèi)是唯一的).但是每個子查詢都必須有一個唯一的別名，這就是您的錯誤消息的來源.

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.

以下是我認為您想要的代碼.注意我從子查詢內(nèi)部刪除了別名——它們是不必要的，盡管它們同樣不會引起任何問題.應該解決您的錯誤的主要更改是將最后 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.
此外，對表、列、數(shù)據(jù)庫等的名稱使用任何關(guān)鍵字是(非常)糟糕的設計.看起來您有一個名為 [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.

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