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

i have a table user(id,name) and a table payments(date,amount,user_id)

我有一个表用户(id,名称)和一个表付款(日期,金额,user_id)

I need a query to display for each user the total sum of payments and the payments in last month.

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

User - totalpayments - monthlypayments

something like this

这样的事情

SELECT id, name from user;

SELECT id,来自用户的名字;

SELECT COUNT(amount) FROM payments WHERE user_id=user.id;

SELECT COUNT(金额)FROM付款WHERE user_id = user.id;

SELECT COUNT(amount) FROM payments WHERE user_id=user.id AND (data<=$timestamptoday AND data>=$timestamp1stday)

SELECT COUNT(金额)FROM付款WHERE user_id = user.id AND(data <= $ timestamptoday AND data> = $ timestamp1stday)

is it possible to do that without doing many queries?

没有做很多查询就可以做到这一点吗?

2 个解决方案

#1

Sure you can do it within one query.

当然你可以在一个查询中完成它。

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

Working DEMO

#2

Absolutely you can do this as a single query:

绝对可以将其作为单个查询执行:

SELECT    user.name, SUM(amount) AS totalpayments, SUM(IF(date <= '$enddate' AND date >= '$startdate'),amount,0) AS monthlypayments
FROM      user LEFT JOIN payments ON user.id = payments.user_id
GROUP BY  user.id
ORDER BY  user.name ASC

Note that I'm using SUM rather than COUNT on the assumption that you're looking for the total amounts of the payments, rather than the number of payments there were.

请注意,我假设您正在查找付款总额而不是付款数量,因此我使用的是SUM而不是COUNT。

#1