单表统计总量当月量 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之前就要考虑到,尽量少使用子查询,如果可以连表查询的就不要使用子查询。