SQL “已添加具有相同键的项目”尝试设置数据集时出现 SSRS 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3911819/
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
"An item with the same key has already been added" Error on SSRS When Trying to Set Dataset
提问by Beginner_Pal
When i try to set the Dataset in SSRS IDE, i get the error you see in the snapshot.
当我尝试在 SSRS IDE 中设置数据集时，出现您在快照中看到的错误。
The query works totally fine in SQL Server Management Studio, i wonder where did i go wrong?!
查询在 SQL Server Management Studio 中完全正常，我想知道我哪里出错了？！
The connection to DB is well established.
与 DB 的连接已建立良好。
OPTIONAL:
可选的：
In case you want to have a look at my query (its too long), i checked it very well. Nothing wrong in it:
如果你想看看我的查询（它太长了），我检查得很好。没有错：
SELECT Customer.customerID, Customer.companyName, CustomerInvoice.dueDate, CustomerInvoice.cuInvoiceID, CustomerQuote.PONumber, Product.productName, CASE WHEN (SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 THEN CustomerQuoteProducts.unitPrice * 1.15
WHEN (SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 THEN CustomerQuoteProducts.unitPrice
ELSE CustomerQuoteProducts.unitPrice
END AS "unitPrice",
CustomerQuoteProducts.qty, CustomerQuoteProducts.isTaxPaid, PaymentMethod.paymMethDesc, CustomerInvoice.customerQuoteID, CustomerInvDetail.paidDate, CustomerInvDetail.clearedDate,
CustomerInvDetail.notes, CustomerInvDetail.sentDate, PaymentExpected.payExpectedTitle, PaymentStatus.paymentStatusTitle,
CASE WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty) * 1.15) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15)) * CustomerQuoteProducts.qty) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15)) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) END AS [TotalPrice], CASE WHEN row_number() OVER (partition BY
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaid > 0 THEN (CustomerShipping.shippingPrice * 1.15)
WHEN CustomerShipping.isTaxPaid <= 0 THEN (CustomerShipping.shippingPrice) END) END AS [ShippingCost],
CASE WHEN row_number() OVER (partition BY
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaidForOrigPr > 0 THEN (CustomerShipping.origShipPrice * 1.15)
WHEN CustomerShipping.isTaxPaidForOrigPr <= 0 THEN (CustomerShipping.origShipPrice) END) END AS [ShippingOrigCost],
CustomerShipping.isTaxPaid,
CustomerShipping.isTaxPaidForOrigPr,
CustomerShipping.shippingDate, CustomerShipping.trackingNumber, ShippingMethod.shippingVia, CustomerShipping.desAddress,
CustomerShipping.desCity, CustomerShipping.desPOBox, CustomerShipping.desPostalCode, CustomerShipping.desProvince, CustomerShipping.descName,
CustomerShipping.packageContent, CustomerShipping.cuShippingID, Country.countryName, CustomerShipping.packageDepth,
CustomerShipping.packageHeight, CustomerShipping.packageWeight, CustomerShipping.packageWidth, CustomerShipping.pickUpLocation
FROM CustomerInvoice INNER JOIN
CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNER JOIN
CustomerQuote ON CustomerQuote.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
CustomerQuoteProducts ON CustomerQuoteProducts.CustomerQuoteID = CustomerQuote.CustomerQuoteID INNER JOIN
CustomerShipping ON CustomerShipping.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
PaymentStatus ON PaymentStatus.paymentStatusID = CustomerInvDetail.paymentStatusID INNER JOIN
Customer ON Customer.CustomerID = CustomerQuote.CustomerID INNER JOIN
Product ON CustomerQuoteProducts.productID = Product.productID INNER JOIN
Country ON Country.countryID = CustomerShipping.countryID INNER JOIN
ShippingMethod ON ShippingMethod.shippingMethodID = CustomerShipping.shippingMethodID INNER JOIN
PaymentExpected ON PaymentExpected.paymentExpectedID = CustomerInvDetail.paymentExpectedID INNER JOIN
PaymentMethod ON PaymentMethod.paymentMethodID = CustomerInvoice.paymentMethodID
WHERE CustomerInvoice.cuInvoiceID = @cuInvID
回答by Amal Khezami
After formatting your script a bit, I noticed that there are 2 columns with the same name that you are selecting. Make sure to change the final name & that every column when you do run your statement in Management Studio has a uniquename.
稍微格式化脚本后，我注意到有 2 列与您选择的名称相同。确保更改最终名称，并确保在 Management Studio 中运行语句时的每一列都具有唯一名称。
That being said, the two columns I noticed have duplicate names are customerquoteproducts.istaxpaidand customershipping.istaxpaid
话虽如此，我注意到有重复名称的两列是customerquoteproducts.istaxpaid和customershipping.istaxpaid
I hope that helps!
我希望这有帮助！
回答by adric
I came across this post today when I received the same issue. My problem how-ever was a little bit different.
当我收到同样的问题时，我今天遇到了这篇文章。然而，我的问题有点不同。
I had a temporary table and an If statement in my SP. So my SP looked something like this:
我的 SP 中有一个临时表和一个 If 语句。所以我的 SP 看起来像这样：
DECLARE @myCheck as int
SET @myCheck = 0
SELECT @myCheck = CASE WHEN [checkfield] = 'xxx' then 1 else 0 end
FROM checktable
WHERE ...
SELECT myfields.*
into #temptable
FROM mytable
WHERE ...
IF @myCheck = 1 --if ssrs does'nt want ot refresh your fields, remove hte if as it does not see past this...
select *
FROM #temptable
LEFT JOIN tableA
ON ...
ELSE
select *
FROM #temptable
LEFT JOIN tableB
ON ...
So in order to "refresh" my fields in SSRS, I commented out the If statement and kept one of my queries with the field names. Just had to remember to add everything back afterwards...
因此，为了“刷新”我在 SSRS 中的字段，我注释掉了 If 语句，并保留了我的一个带有字段名称的查询。只需要记住之后将所有内容添加回来......
Using Visual Studio 2012 / SQL 2012
使用 Visual Studio 2012 / SQL 2012
