首页 » 开发 » sql » sql练习题1,分列展示某一筛选条件

sql练习题1,分列展示某一筛选条件

 

源数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE t_user_payment(
id VARCHAR(20),
amount INT ,
order_type VARCHAR(20),
payment_chnl VARCHAR(20));
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'kuaiche','wechat');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'shunfengche','wechat');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'zhuanche','wechat');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'zhuanche','wechat');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'kuaiche','alipay');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'zhuanche','alipay');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'shunfengche','alipay');
INSERT INTO t_user_payment(id, amount, order_type, payment_chnl)
VALUES('1',10,'kuaiche','other');


代码

1
2
3
4
5
6
select payment_chnl,
sum(if (order_type = 'kuaiche',tup.amount,0)) as kuaiche_amt,
sum(if (order_type = 'zhuanche',tup.amount,0)) as zhuanche_amt,
sum(if (order_type = 'shunfengche',tup.amount,0)) as shunfengche_amt
from t_user_payment tup
group by payment_chnl;

原文链接:sql练习题1,分列展示某一筛选条件,转载请注明来源!

1