单表统计总量当月量 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
	(
	SELECT
		sum( 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,
	(
	SELECT
		sum( 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
		(
		SELECT
			mcb.type_detail,
			sum( money ) money 
		FROM
			mall_customer_bill mcb 
		WHERE
			mcb.user_id = 20 
			AND FROM_UNIXTIME( mcb.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' ) 
		GROUP BY
			mcb.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) money
		from mall_customer_bill mcb
		where
				mcb.user_id = 20
		group by mcb.type_detail

可以在此基础上直接查询

	SELECT
		sum( 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 
	FROM
		mall_customer_bill mcb 
	WHERE
		mcb.user_id = 20 
	GROUP BY
		mcb.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) compensationAmount
	from 
	(
		select mcb.type_detail,sum(money) money
		from mall_customer_bill mcb
		where
				mcb.user_id = 20
		group by mcb.type_detail
	) b

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

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

	SELECT
			COALESCE (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 
	FROM
		mall_customer_bill mcb 
	WHERE
		mcb.user_id = 20 
		AND FROM_UNIXTIME( mcb.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' ) 
	GROUP BY
		mcb.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
	(
	SELECT
		sum( 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,
	(
	SELECT
		sum( 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
		(
		SELECT
			mcb.type_detail,
			sum( money ) money 
		FROM
			mall_customer_bill mcb 
		WHERE
			mcb.user_id = 20 
			AND FROM_UNIXTIME( mcb.create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' ) 
		GROUP BY
			mcb.type_detail 
		) t 
	) nowadays
	

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