如何制作一些列？

x4shl7ld 于 2021-06-19 发布在 Mysql

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

我需要在几列中显示结果？
样本数据

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     bank

我需要结果：

diapason     total_amount    card     cash     bank
0-10              10     
10 and more       10

实查询

select IFnull(t.diapason,'total') as diapason, t.total_amount as 
total_amount, t.cash, t.bank 
FROM 
(SELECT CASE
when p2.amount<=10 then '0-10'
when p2.amount>10 then '10 +' END AS diapason, 
sum(p1.amount) as total_amount,
SUM(CASE WHEN p1.sale_type='cash' THEN p1.amount ELSE 0 END) as cash,
SUM(CASE WHEN p1.sale_type='bank' THEN p1.amount ELSE 0 END) as bank
FROM 
(SELECT distinct user_id, SUM(amount) AS amount, sale_type FROM payments 
where payment_time between '2018-04-12' and '2018-04-18' group by user_id) 
p1 
inner JOIN 
(SELECT DISTINCT user_id, sum(amount) AS amount FROM payments group by 
user_id) p2 
on p1.user_id = p2.user_id 
GROUP BY diapason WITH ROLLUP) t;

这是一个真实的查询，但给出了错误的结果

sql mysql Join Database select

来源：https://stackoverflow.com/questions/52239394/how-to-make-a-few-columns

1条答案

按热度按时间

wztqucjr1#

你可以试试这个。
我想你在找条件聚合函数
架构（mysql v5.7）

CREATE TABLE payments(
  user_id INT,
  amount INT,
  payment_time DATE,
  sale_type VARCHAR(50)
);

     INSERT INTO payments VALUES (1,20,'2011-01-31','card');
     INSERT INTO payments VALUES (1,10,'2012-01-02','cash');
     INSERT INTO payments VALUES (3,10,'2012-01-03','card');
     INSERT INTO payments VALUES (4,15,'2012-02-05','bank');

查询#1

select (case when p2.amount <=10 then '0-10'
    else '10 and more' end) diapason,
       sum(p1.amount) total_amount,
       SUM(CASE WHEN p1.sale_type='card' THEN p1.amount ELSE 0 END) card,
       SUM(CASE WHEN p1.sale_type='cash' THEN p1.amount ELSE 0 END) cash,
       SUM(CASE WHEN p1.sale_type='bank' THEN p1.amount ELSE 0 END) bank
from 
(
  select *
  from payments
  where payment_time between '2012-01-01' and '2012-01-30' group by user_id, sale_type 
) p1
join (select user_id, sum(amount) amount
  from payments
  group by user_id) p2
on p1.user_id = p2.user_id
GROUP BY (case when p2.amount <=10 then '0-10'
    else '10 and more' end);

| diapason    | total_amount | card | cash | bank |
| ----------- | ------------ | ---- | ---- | ---- |
| 0-10        | 10           | 10   | 0    | 0    |
| 10 and more | 10           | 0    | 10   | 0    |

db fiddle视图

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

我来回答

如何制作一些列？

1条答案

相关问题

热门标签

最新问答