同列分类求和并按其他列分组
| userid |
type |
income |
| 1 |
微信 |
4 |
| 2 |
支付宝 |
2 |
| 2 |
微信 |
6 |
| 1 |
支付宝 |
7 |
| ... |
... |
... |
问题描述：如上表 INCOME，要分别统计每个用户微信、支付宝的收入总和，结果格式如下。(即按 微信、支付宝分类，按照userid分group求和)
| userid |
微信 |
支付宝 |
| 1 |
xxx |
xxx |
| 2 |
xxx |
xxx |
//sqlalchemy
db.session.query(INCOME.userid,
func.sum(case([(INCOME.type=='微信',INCOME.income)],else_=0)).label('income_wx'),
func.sum(case([(INCOME.type=='支付宝',INCOME.income)],else_=0)).label('income_ali'),
).group_by(INCOME.userid).all()
//sql
SELECT userid,
SUM(CASE WHEN type='微信' THEN income ELSE 0 END) AS income_wx,
SUM(CASE WHEN type='支付宝' THEN income ELSE 0 END) AS income_ali
FROM INCOME GROUP BY userid
网友评论