MySQL SUM() 与 JOIN 和 LIMIT

Question

相关的模式和查询位于 SQL fiddle http://sqlfiddle.com/#!2/312d3/16

下面的 JOIN 查询得到了我想要的结果。

SELECT p.*, payer.username as payer_username, payee.username as
payee_username FROM (`payments_history` p)  JOIN (SELECT * FROM users)
AS payer ON `payer`.`user_id` = `p`.`payer_id`  JOIN (SELECT * FROM
users) AS payee ON `payee`.`user_id` = `p`.`payee_id`  ORDER BY
`p`.`created_timestamp`;

对所有行的“p.amount”列执行 sum() 也可以正常工作。

SELECT SUM(p.amount)  FROM (`payments_history` p)  JOIN (SELECT * FROM
users) AS payer ON `payer`.`user_id` = `p`.`payer_id`  JOIN (SELECT *
FROM users) AS payee ON `payee`.`user_id` = `p`.`payee_id`  ORDER BY
`p`.`created_timestamp`;

但是对每页上的行(偏移量、限制)的同一列执行 sum() 会返回一个空结果(我希望获得每页上的行的“金额”列的总和)。

SELECT SUM(p.amount)  FROM (`payments_history` p)  JOIN (SELECT * FROM
users) AS payer ON `payer`.`user_id` = `p`.`payer_id`  JOIN (SELECT *
FROM users) AS payee ON `payee`.`user_id` = `p`.`payee_id`  ORDER BY
`p`.`created_timestamp` limit 0,2;

同样在 sum() 查询中，当偏移量从大于 0 的任何值开始(尝试 LIMIT 2,2)时，它会返回空结果。

我做错了什么？谢谢。

Answer 1

尝试一下:

SELECT SUM(l.amount)  FROM (
  SELECT p.amount FROM payments_history p
  INNER JOIN users payer ON payer.user_id=p.payer_id
  INNER JOIN users payee ON payee.user_id=p.payee_id
  ORDER BY p.created_timestamp
  LIMIT 0,10
) l

我不确定您是否计划向查询中添加其他字段，或者在 WHERE 子句中添加更多条件，但目前将这些联接放在其中似乎并没有多大用处。