Mysql 子选择与 SUM() 和分组依据

Question

我正在尝试创建一个查询，该查询在需要 SUM 函数和分组依据的子查询中执行计算。我的查询返回错误“子查询返回多于 1 行”。本质上，我试图返回每个订单的“到期”金额。如果订单总计大于付款表中的 total_collected(对于该 order_id)的总和，则会有应付金额。这是查询:

SELECT o.order_id
     , o.server
     , o.subtotal
     , o.discount
     , o.tax, o.total
     , (SELECT (o.total - SUM(p.total_collected)) 
            from orders o 
            join payments p 
              on o.order_id = p.order_id 
        group by p.order_id) as 'Due'
  FROM orders o 
  join payments p 
    on o.order_id = p.order_id
WHERE...;

我不能在子选择中包含“p.order_id”，因为它应该只包含一列。我明白为什么我会收到错误，我只是不知道如何让子选择仅在每个 order_id 的基础上执行 SUM。

Answer 1

在不改变结构的情况下，我认为子查询正在查看订单/付款表中的所有数据。我认为您需要像这样过滤它以仅查看相关的 order_id。

(我还在订单总额周围添加了一个 SUM，因为我很确定没有它会产生不同的错误。)

SELECT o.order_id
     , o.server
     , o.subtotal
     , o.discount
     , o.tax
     , o.total
     , (SELECT (SUM(o2.total) - SUM(p.total_collected))
            from orders o2 
            JOIN payments p 
              ON o2.order_id = p.order_id
            WHERE o2.order_id = o.order_id) as 'Due'
    FROM orders o
WHERE...;

尽管如此，如果您调整它以便它使用连接而不是子查询，我认为您会获得更好的性能。像这样:

SELECT o.order_id
     , o.server
     , o.subtotal
     , o.discount
     , o.tax
     , o.total
     , o.total - c.Collected AS 'Due'
    FROM orders o
    JOIN (
        SELECT p2.order_id, SUM(p2.total_collected) AS 'Collected'
          FROM payments p2
        GROUP BY p2.order_id) AS c
      ON o.order_id = c.order_id
WHERE...;