mysql - 无法弄清楚我错过了什么？

Question

我使用一个子查询来返回折扣大于 15% 的所有订单的货币值(value)。列出最后的 orderid 和订单值，最高值位于顶部

这是我输入的内容:

SELECT SUM(od.orderid) As OrderID,
AS [Order Values]
FROM [Order Details] od
WHERE od.Discount =
    (SELECTod.Discount
    FROM [Order Details] od
    GROUP BY od.discount
    HAVING od.discount >.15)
GROUP BY od.quantity, od.discount, od.UnitPrice
ORDER BY [Order Values] ASC;

这是我得到的:

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

我错过了什么？

Answer 1

错误出现在 WHERE 子句中。您使用 = 表示多个值，但该运算符仅允许使用 1 个值。所以改变这个:

WHERE od.Discount =
    (SELECT od.Discount
    FROM [Order Details] od
    GROUP BY od.discount
    HAVING od.discount >.15)

对此:

WHERE od.Discount IN
    (SELECT od.Discount
    FROM [Order Details] od
    GROUP BY od.discount
    HAVING od.discount >.15)

或者这个:

WHERE od.Discount =
    (SELECT TOP 1 od.Discount
    FROM [Order Details] od
    GROUP BY od.discount
    HAVING od.discount >.15
    ORDER BY od.Discount DESC)