mysql - 如何选择某些 'id'的和并将结果按组划分

Question

在此查询中，我尝试选择用户已支付的付款金额:

SELECT *
from (select IFnull(t.diapason,'total') as diapason,
t.total_amount as total_amount 

FROM 
(SELECT p.user_id, p.amount as total_amount, CASE
when amount<=100 then '0-100'
when amount>100 then '100...' END AS diapason
FROM (SELECT distinct payments.user_id, SUM(amount) AS amount 
FROM payments inner JOIN (SELECT DISTINCT user_id FROM payments where 
     payment_time between '2000-01-01' and '2001-01-01') a ON 
payments.user_id = a.user_id 
GROUP BY payments.user_id) p) t  
GROUP BY diapason WITH ROLLUP) as t1  
ORDER BY total_amount desc;

但是我得到的结果是错误的。我应该更改什么才能找出 2000-01-01 至 2001-01-01 期间的付款金额。由于用户曾经进行过付款，结果必须按组划分。

这些是表格事件和付款。

activity
user_id   login_time
1         2000-01-01
2         2000-03-01
....

payments
user_id     payment_time    amount
1          2000-05-04        10
1          2000-03-01        20
2          2000-04-05        5
...

通常，按每个用户曾经支付的总金额组成组。例如，如果他支付了 50 美元 - 他属于“0-100”组。如果他支付了 500 - 他属于“100...”组，但现在我需要知道每个组中用户支付的总金额。

如果您愿意提供帮助，谢谢!

Answer 1

我认为您需要 SUM 或 AVG 之一:

SELECT user_id,
       SUM(Amount) `sum`,
       AVG(Amount) `avg`
FROM payments 
WHERE payment_time BETWEEN '2000-01-01' AND '2001-01-01'
GROUP BY ser_id

或此(2000-01-01 和 2001-01-01 之间的金额总和除以特定用户的记录总数):

SELECT user_id,
       SUM(CASE WHEN payment_time BETWEEN '2000-01-01' AND '2001-01-01' THEN Amount ELSE 0 END) / COUNT(*) `customAvg`
FROM payments 
WHERE payment_time BETWEEN '2000-01-01' AND '2001-01-01'
GROUP BY ser_id