单表统计总量当月量 sql语句

单表统计总量当月量 sql语句

前端实现效果

sql

	SELECT COALESCE( transactionTotalAmount, 0 ) transactionTotalAmount,COALESCE ( rechargeTotalAmount, 0 ) rechargeTotalAmount,COALESCE ( refundAmount, 0 ) refundAmount,COALESCE ( compensationAmount, 0 ) compensationAmount,COALESCE ( currentMonthTransactionAmount, 0 ) currentMonthTransactionAmount,COALESCE ( currentMonthRechargeTotalAmount, 0 ) currentMonthRechargeTotalAmount 
FROM(SELECTsum( CASE WHEN t.type_detail IN ( 2, 3 ) THEN money ELSE 0 END ) transactionTotalAmount,sum( CASE WHEN t.type_detail = 1 THEN money ELSE 0 END ) rechargeTotalAmount,sum( CASE WHEN t.type_detail = 8 THEN money ELSE 0 END ) refundAmount,sum( CASE WHEN t.type_detail = 4 THEN money ELSE 0 END ) compensationAmount FROM( SELECT mcb.type_detail, sum( money ) money FROM mall_customer_bill mcb WHERE mcb.user_id = 20 GROUP BY mcb.type_detail ) t ) total,(SELECTsum( CASE WHEN t.type_detail IN ( 2, 3 ) THEN money ELSE 0 END ) currentMonthTransactionAmount,sum( CASE WHEN t.type_detail = 1 THEN money ELSE 0 END ) currentMonthRechargeTotalAmount FROM(SELECTmcb.type_detail,sum( money ) money FROMmall_customer_bill mcb WHEREmcb.user_id = 20 AND FROM_UNIXTIME( mcb.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' ) GROUP BYmcb.type_detail ) t ) nowadays

表名：mall_customer_bill（账单表）

表结构 ：

交易金额：type_detail列 的2+3

充值金额：type_detail列 的1

退款金额：type_detail列 的8

赔偿金额：type_detail列 的4

根据user_id查询总额

根据当月时间查询本月金额

思路：

首先根据user_id查询到对应的账单，然后根据type_detail进行分组，求出每一个type_detail的和。

	select mcb.type_detail,sum(money) moneyfrom mall_customer_bill mcbwheremcb.user_id = 20group by mcb.type_detail

可以在此基础上直接查询

	SELECTsum( CASE WHEN mcb.type_detail IN ( 2, 3 ) THEN money ELSE 0 END ) transactionTotalAmount,sum( CASE WHEN mcb.type_detail = 1 THEN money ELSE 0 END ) rechargeTotalAmount,sum( CASE WHEN mcb.type_detail = 8 THEN money ELSE 0 END ) refundAmount,sum( CASE WHEN mcb.type_detail = 4 THEN money ELSE 0 END ) compensationAmount FROMmall_customer_bill mcb WHEREmcb.user_id = 20 GROUP BYmcb.type_detail 

也可以把他当做一个子查询，根据他的结果再查询

	select sum(case when b.type_detail in (2,3) then money else 0 end) transactionTotalAmount,sum(case when b.type_detail = 1 then money else 0 end) rechargeTotalAmount,sum(case when b.type_detail = 8 then money else 0 end) refundAmount,sum(case when b.type_detail = 4 then money else 0 end) compensationAmountfrom (select mcb.type_detail,sum(money) moneyfrom mall_customer_bill mcbwheremcb.user_id = 20group by mcb.type_detail) b

虽然获得的结果都一样，但是第一种比第二种查询的速度要快。

获得总量之后，当月的数量就是在此基础上添加条件

	SELECTCOALESCE (sum( CASE WHEN mcb.type_detail IN ( 2, 3 ) THEN money ELSE 0 END ),0) currentMonthTransactionAmount,COALESCE (sum( CASE WHEN mcb.type_detail = 1 THEN money ELSE 0 END ),0) currentMonthRechargeTotalAmount FROMmall_customer_bill mcb WHEREmcb.user_id = 20 AND FROM_UNIXTIME( mcb.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' ) GROUP BYmcb.type_detail 

因为使用的是时间戳所以要用FROM_UNIXTIME()函数转换。

然后把这两个结果合并起来就行了。

SELECT COALESCE( transactionTotalAmount, 0 ) transactionTotalAmount,COALESCE ( rechargeTotalAmount, 0 ) rechargeTotalAmount,COALESCE ( refundAmount, 0 ) refundAmount,COALESCE ( compensationAmount, 0 ) compensationAmount,COALESCE ( currentMonthTransactionAmount, 0 ) currentMonthTransactionAmount,COALESCE ( currentMonthRechargeTotalAmount, 0 ) currentMonthRechargeTotalAmount 
FROM(SELECTsum( CASE WHEN t.type_detail IN ( 2, 3 ) THEN money ELSE 0 END ) transactionTotalAmount,sum( CASE WHEN t.type_detail = 1 THEN money ELSE 0 END ) rechargeTotalAmount,sum( CASE WHEN t.type_detail = 8 THEN money ELSE 0 END ) refundAmount,sum( CASE WHEN t.type_detail = 4 THEN money ELSE 0 END ) compensationAmount FROM( SELECT mcb.type_detail, sum( money ) money FROM mall_customer_bill mcb WHERE mcb.user_id = 20 GROUP BY mcb.type_detail ) t ) total,(SELECTsum( CASE WHEN t.type_detail IN ( 2, 3 ) THEN money ELSE 0 END ) currentMonthTransactionAmount,sum( CASE WHEN t.type_detail = 1 THEN money ELSE 0 END ) currentMonthRechargeTotalAmount FROM(SELECTmcb.type_detail,sum( money ) money FROMmall_customer_bill mcb WHEREmcb.user_id = 20 AND FROM_UNIXTIME( mcb.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' ) GROUP BYmcb.type_detail ) t ) nowadays

虽然实现了功能但是这种查询速度很慢，有些查询在项目初期就要规划好，一些数据量会变得很大的表，在写sql之前就要考虑到，尽量少使用子查询，如果可以连表查询的就不要使用子查询。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！