Mysql 查询 - 操作数应包含 1 列

Question

我在这里讨论了几个话题，但我无法让它发挥作用。对于每个“底池”，我想获得付款次数以及与该底池相关的全部佣金(所有付款佣金的总和)。

SELECT id, id_user,
       ( SELECT IFNULL( SUM(amount), 0 ) AS collectedA, 
                IFNULL( SUM(commission), 0 ) AS commission 
                FROM payment AS pay WHERE pay.id_pot = pot.id AND pay.stat = 1 ) FROM pot

上面给了我标题中提到的错误。我知道我们不能用这种子查询选择两列。我尝试通过 LEFT OUTER JOIN 但无法正常工作。

SELECT id, id_user, 
       IFNULL( SUM(payment.amount), 0 ) AS collectedA, 
       IFNULL( SUM(payment.commission), 0 ) AS commission
FROM pot LEFT OUTER JOIN payment ON payment.id_pot = pot.id AND payment.stat = 1

但这给了我奇怪的结果......

Answer 1

您必须LEFT JOIN 到已经聚合 的派生表，如下所示:

SELECT id, id_user, collectedA, commission 
FROM pot 
LEFT JOIN (
   SELECT id_pot,
          IFNULL( SUM(amount), 0 ) AS collectedA, 
          IFNULL( SUM(commission), 0 ) AS commission 
   FROM payment  
   WHERE stat = 1 
   GROUP BY id_pot
) AS pay pay.id_pot = pot.id

在子查询中执行的 GROUP BY 保证您将根据 pot.id 得到恰好一条记录。