php - 从其他子查询向 mysql 查询添加一列

Question

我有一张 table user(id,name)和一张 table payments(date,amount,user_id)

我需要一个查询来显示每个用户的付款总额和上个月的付款。

User - totalpayments - monthlypayments

像这样

1. SELECT id, name from user;
2. SELECT COUNT(amount) FROM payments WHERE user_id=user.id;
3. SELECT COUNT(amount) FROM payments WHERE user_id=user.id AND (data<=$timestamptoday AND data>=$timestamp1stday)

是否可以在不进行多次查询的情况下做到这一点？

Answer 1

当然，您可以在一次查询中完成。

SELECT
    u.ID,
    u.Name,
    SUM(p.amount),
    SUM(CASE
          WHEN p.date <= '2013-02-01' AND p.date >= '2013-01-01' THEN p.amount
          ELSE 0
        END)
FROM
    Users u
JOIN
    Payments p ON u.ID = p.UserID
GROUP BY
    u.ID,
    u.Name

工作 DEMO