mysql - 找不到我想念的东西吗？

我使用一个子查询来返回所有折扣大于15％的订单的货币价值。
列出订单编号和最后的订单值，并在顶部显示最高值

这是我输入的内容：

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;

这是我得到的：


  消息512，级别16，状态1，第1行
  子查询返回的值超过1。当子查询遵循=，！=，，> =>或将子查询用作表达式时，不允许这样做。


我想念什么？

最佳答案

无论如何，您实际上确实不需要子查询

 SELECT OrderID, SUM(UnitPrice * Quantity) OrderTotal
 FROM dbo.[Order Details]
 WHERE Discount > 0.15
 GROUP BY OrderID
 ORDER BY OrderTotal DESC

与子查询

 SELECT OrderID, SUM(UnitPrice * Quantity) OrderTotal
 FROM dbo.[Order Details]
 WHERE OrderID IN (SELECT OrderID
                   FROM dbo.[Order Details]
                   WHERE Discount > 0.15)
 GROUP BY OrderID
 ORDER BY OrderTotal DESC

使用EXISTS运算符

 SELECT OD.OrderID, SUM(OD.UnitPrice * OD.Quantity) OrderTotal
 FROM dbo.[Order Details] OD
 WHERE EXISTS (SELECT 1
               FROM dbo.[Order Details]
               WHERE OrderID = OD.OrderID
               AND Discount > 0.15)
 GROUP BY OD.OrderID
 ORDER BY OrderTotal DESC