如何选择某些“id”的和并按组划分结果

iyzzxitl 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(304)

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

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…”但现在我需要知道用户在每个组的付款总额。
谢谢你的帮忙！

sql mysql Join Database select

来源：https://stackoverflow.com/questions/52218932/how-to-select-sums-of-certain-id-and-devide-the-result-by-groups

2条答案

按热度按时间

8wigbo561#

如果我理解正确，就不需要所有这些子查询。简单 sum 以及 group by 应该这样做：

SELECT t1.user_id, sum(t2.amount) as total, IF(sum(t2.amount)<=100, '0-100',  '100...') as grp 
FROM t1
INNER JOIN t2 on t1.user_id = t2.user_id
WHERE t2.payment_time between '2000-01-01' and '2001-01-01'
GROUP BY t1.user_id

赞(0）回复(0）举报 2021-06-20

yqkkidmi2#

我想你需要一个 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

或者这个 Amount 在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

赞(0）回复(0）举报 2021-06-20

我来回答

如何选择某些“id”的和并按组划分结果

2条答案

相关问题

热门标签

最新问答