SQL Server 子查询返回超过 1 个值.当子查询遵循 =、!=、<、<=、>、>= 时，这是不允许的

2022-01-31 00:00:00 sql tsql sql-server

我运行以下查询:

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

我收到以下错误:

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

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.

有什么想法吗?

推荐答案

试试这个:

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'

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

This will return multiple rows that are identical except for the cost column. 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.

相关文章