如何划分分组计算结果？

ruarlubt 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(299)

很好的一天！你能帮我问一下吗？
我有一张“付款”表：

payments
user_id     amount     payment_time    sale_type
1           20          31.01.2011      card
1           10          02.01.2012      cash
3           10          03.01.2012      card
4           15          05.02.2012      cash
...and so on

任务是选择2012年1月1日至2012年1月30日的总付款金额，并根据用户曾经支付的金额将该金额除以组。
组为“0-10”-如果总和为0-10$“10和更多”-如果总和>10$。
我的代码：

SELECT * from (select IFnull(t.diapason,'total') as diapason, total_amount 
FROM 
    (SELECT p.user_id, p.amount as total_amount, CASE
        when amount<=10 then '0-10'
        when amount>10 then '10 and more' 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 '2012-01-01' 
                     and '2012-01-30') 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;

这里怎么了？
预期产量

diapason     total_amount
0-10              10     - here is user with id 3 
10 and more       10     - here is user with id 1 (because he ever payed 30)
total

sql mysql Join Database select

来源：https://stackoverflow.com/questions/52225078/how-to-divide-the-result-of-calculation-on-groups

1条答案

按热度按时间

3b6akqbq1#

尝试此查询-

select case when p2.amount <=10 then '0-10'
        else '10 and more' end diapason
      ,p1.amount "total amount"
      ,p1.payment_by_card
      ,p1.cash
from (select user_id, sum(amount) amount, payment_by_card, cash
      from payments
      where payment_time between '2012-01-01' and '2012-01-30'
      group by user_id, payment_by_card, cash) p1
join (select user_id, sum(amount) amount
      from payments
      group by user_id) p2
on p1.user_id = p2.user_id

这是小提琴-http://www.sqlfiddle.com/#!2008年9月22日

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

我来回答

如何划分分组计算结果？

1条答案

相关问题

热门标签

最新问答