SQL Server 子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 时，这是不允许的

SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

提问by Anilkumar

I run the following query:

我运行以下查询：

SELECT 
   orderdetails.sku,
   orderdetails.mf_item_number,
   orderdetails.qty,
   orderdetails.price,
   supplier.supplierid,
   supplier.suppliername,
   supplier.dropshipfees,
   cost = (SELECT supplier_item.price
           FROM   supplier_item,
                  orderdetails,
                  supplier
           WHERE  supplier_item.sku = orderdetails.sku
                  AND supplier_item.supplierid = supplier.supplierid)
FROM   orderdetails,
       supplier,
       group_master
WHERE  invoiceid = '339740'
       AND orderdetails.mfr_id = supplier.supplierid
       AND group_master.sku = orderdetails.sku  

I get the following error:

我收到以下错误：

Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

消息 512，级别 16，状态 1，第 2 行子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。

Any ideas?

有任何想法吗？

采纳答案by Jeffrey L Whitledge

Try this:

尝试这个：

SELECT
    od.Sku,
    od.mf_item_number,
    od.Qty,
    od.Price,
    s.SupplierId,
    s.SupplierName,
    s.DropShipFees,
    si.Price as cost
FROM
    OrderDetails od
    INNER JOIN Supplier s on s.SupplierId = od.Mfr_ID
    INNER JOIN Group_Master gm on gm.Sku = od.Sku
    INNER JOIN Supplier_Item si on si.SKU = od.Sku and si.SupplierId = s.SupplierID
WHERE
    od.invoiceid = '339740'

This will return multiple rows that are identical except for the costcolumn. Look at the different cost values that are returned and figure out what is causing the different values. Then ask somebody which cost value they want, and add the criteria to the query that will select that cost.

这将返回除cost列之外相同的多行。查看返回的不同成本值并找出导致不同值的原因。然后询问某人他们想要哪个成本值，并将条件添加到将选择该成本的查询中。

回答by jk.

Check to see if there are any triggers on the table you are trying to execute queries against. They can sometimes throw this error as they are trying to run the update/select/insert trigger that is on the table.

检查您尝试对其执行查询的表上是否有任何触发器。他们有时会在尝试运行表上的更新/选择/插入触发器时抛出此错误。

You can modify your query to disable then enable the trigger if the trigger DOES NOTneed to be executed for whatever query you are trying to run.

您可以修改您的查询，以禁用再启用触发若触发DOES NOT需要任何查询，你正在尝试运行被执行。

ALTER TABLE your_table DISABLE TRIGGER [the_trigger_name]

UPDATE    your_table
SET     Gender = 'Female'
WHERE     (Gender = 'Male')

ALTER TABLE your_table ENABLE TRIGGER [the_trigger_name]

回答by Diyar sereroy

SELECT COLUMN 
    FROM TABLE 
WHERE columns_name
    IN ( SELECT COLUMN FROM TABLE WHERE columns_name = 'value');

note: when we are using sub-query we must focus on these points:

注意：当我们使用子查询时，我们必须关注以下几点：

1. if our sub query returns 1 value in this case we need to use (=,!=,<>,<,>....)
2. else (more than one value), in this case we need to use (in, any, all, some )

1. 如果我们的子查询在这种情况下返回 1 个值，我们需要使用 (=,!=,<>,<,>....)
2. else（多个值），在这种情况下我们需要使用 (in, any, all, some )

回答by Mayo

cost = Select Supplier_Item.Price from Supplier_Item,orderdetails,Supplier 
   where Supplier_Item.SKU=OrderDetails.Sku and 
      Supplier_Item.SupplierId=Supplier.SupplierID

This subquery returns multiple values, SQL is complaining because it can't assign multiple values to cost in a single record.

此子查询返回多个值，SQL 抱怨，因为它无法在单个记录中为成本分配多个值。

Some ideas:

一些想法：

1. Fix the data such that the existing subquery returns only 1 record
2. Fix the subquery such that it only returns one record
3. Add a top 1 and order by to the subquery (nasty solution that DBAs hate - but it "works")
4. Use a user defined function to concatenate the results of the subquery into a single string

1. 修复数据，使得现有子查询只返回 1 条记录
2. 修复子查询，使其只返回一条记录
3. 在子查询中添加一个 top 1 和 order by（DBA 讨厌的讨厌的解决方案 - 但它“有效”）
4. 使用用户定义的函数将子查询的结果连接成单个字符串

回答by egrunin

Either your data is bad, or it's not structured the way you think it is.Possibly both.

要么你的数据不好，要么没有你想象的那样结构化。可能两者都有。

To prove/disprove this hypothesis, run this query:

要证明/反驳此假设，请运行以下查询：

SELECT * from
(
    SELECT count(*) as c, Supplier_Item.SKU
    FROM Supplier_Item
    INNER JOIN orderdetails
        ON Supplier_Item.sku = orderdetails.sku
    INNER JOIN Supplier
        ON Supplier_item.supplierID = Supplier.SupplierID
    GROUP BY Supplier_Item.SKU
) x
WHERE c > 1
ORDER BY c DESC

If this returns just a few rows, then your data is bad. If it returns lotsof rows, then your data is not structured the way you think it is.(If it returns zero rows, I'm wrong.)

如果这仅返回几行，则您的数据是 bad。如果它返回很多行，那么您的数据的结构并不像您认为的那样。（如果它返回零行，我错了。）

I'm guessing that you have orders containing the same SKUmultiple times (two separate line items, both ordering the same SKU).

我猜您的订单SKU多次包含相同的订单（两个单独的订单项，都订购相同SKU）。

回答by HLGEM

The fix is to stop using correlated subqueries and use joins instead. Correlated subqueries are essentially cursors as they cause the query to run row-by-row and should be avoided.

解决方法是停止使用相关子查询并改用连接。相关子查询本质上是游标，因为它们会导致查询逐行运行，应避免使用。

You may need a derived table in the join in order to get the value you want in the field if you want only one record to match, if you need both values then the ordinary joinwill do that but you will get multiple records for the same id in the results set. If you only want one, you need to decide which one and do that in the code, you could use a top 1with an order by, you could use max(), you could use min(), etc, depending on what your real requirement for the data is.

如果您只想匹配一条记录，您可能需要在连接中使用派生表来获取您想要的字段值，如果您需要两个值，那么普通join会这样做，但您将获得相同 ID 的多条记录在结果集中。如果您只想要一个，则需要决定哪一个并在代码中执行此操作，您可以将 atop 1与 an 一起使用order by，您可以使用max()，您可以使用min()等，具体取决于您对数据的真正要求是什么。

回答by JAN

I had the same problem , I used ininstead of =, from the Northwinddatabase example :

我遇到了同样的问题，我使用了数据库示例中的in而不是：=Northwind

Query is : Find the Companies that placed orders in 1997

查询是：查找 1997 年下订单的公司

Try this :

尝试这个 ：

SELECT CompanyName
    FROM Customers
WHERE CustomerID IN (
                        SELECT CustomerID 
                            FROM Orders 
                        WHERE YEAR(OrderDate) = '1997'
                    );

Instead of that :

取而代之的是：

SELECT CompanyName
    FROM Customers
WHERE CustomerID =
(
    SELECT CustomerID 
        FROM Orders 
    WHERE YEAR(OrderDate) = '1997'
);

回答by cjk

The select statement in the cost part of your select is returning more than one value. You need to add more where clauses, or use an aggregation.

选择的成本部分中的选择语句返回多个值。您需要添加更多 where 子句，或使用聚合。

回答by Dave Costa

The error implies that this subquery is returning more than 1 row:

该错误意味着此子查询返回超过 1 行：

(Select Supplier_Item.Price from Supplier_Item,orderdetails,Supplier where Supplier_Item.SKU=OrderDetails.Sku and Supplier_Item.SupplierId=Supplier.SupplierID )

You probably don't want to include the orderdetails and supplier tables in the subquery, because you want to reference the values selected from those tables in the outer query. So I think you want the subquery to be simply:

您可能不希望在子查询中包含 orderdetails 和供应商表，因为您希望在外部查询中引用从这些表中选择的值。所以我认为您希望子查询简单：

(Select Supplier_Item.Price from Supplier_Item where Supplier_Item.SKU=OrderDetails.Sku and Supplier_Item.SupplierId=Supplier.SupplierID )

I suggest you read up on correlated vs. non-correlated subqueries.

我建议您阅读相关和非相关子查询。

回答by KP Taylor

As others have suggested, the best way to do this is to use a join instead of variable assignment. Re-writing your query to use a join (and using the explicit join syntax instead of the implicit join, which was also suggested--and is the best practice), you would get something like this:

正如其他人所建议的那样，最好的方法是使用连接而不是变量赋值。重新编写查询以使用连接（并使用显式连接语法而不是隐式连接，这也是建议的 - 并且是最佳实践），您将得到如下内容：

select  
  OrderDetails.Sku,
  OrderDetails.mf_item_number,
  OrderDetails.Qty,
  OrderDetails.Price,
  Supplier.SupplierId, 
  Supplier.SupplierName,
  Supplier.DropShipFees, 
  Supplier_Item.Price as cost
from 
  OrderDetails
join Supplier on OrderDetails.Mfr_ID = Supplier.SupplierId
join Group_Master on Group_Master.Sku = OrderDetails.Sku 
join Supplier_Item on 
  Supplier_Item.SKU=OrderDetails.Sku and Supplier_Item.SupplierId=Supplier.SupplierID 
where 
  invoiceid='339740'