1、需求描述：
这次需求对系统消费数据进行一次数据统计，做出一张报表要求通过时间查询出以往的数据，已经进行一个简单合计和统计，分为明细表和汇总表
2、需求分析、解决方案：
1）、根据甲方提供的数据计算公式把数据进行一个计算，知道初始数据从何来
2）、通过mysql中的子查询、left join、union all、别名等，直接查询出所需要的数据
3）、一开始想用业务代码实现合计和统计，后来同事提醒既然要sql都是要执行的，何不在sql中就把要计算的值都计算出来，所以一般能用sql解决的事就不用代码来了
3、直接上‘硬菜’‘sql’
报表明细表sql
( SELECT
record.CREATETIME,
tf_memberinfo.MI_NAME,
tf_memberinfo.MI_PHONE,
tf_memberinfo.MI_NO,
'--' AS 'OPERATE_MACHINE_NO',
record.MONEY,
record.AFTER_MONEY,
record.CASH_MONEY,
record.SUBSIDY_MONEY,
record.REMARK,
record.TYPE,
tf_userinfo.UNAME AS 'CASHIER'
FROM
tf_memberinfo
INNER JOIN (
SELECT
CASH_MONEY AS 'MONEY',
CASH_ORIGINALAMOUNT AS 'AFTER_MONEY',
CASH_MONEY,
SUBSIDY_MONEY,
RECHARGE_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'现金交费' AS 'REMARK',
'加款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_member_recharge_record
WHERE
RECHARGE_TYPE IN ( '0', '4' )
AND REFUND_STATUS = '0'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
CARD_COST AS 'MONEY',
'0.00' AS 'AFTER_MONEY',
'0.00' AS 'CASH_MONEY',
'0.00' AS 'SUBSIDY_MONEY',
ORDER_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'退还押金' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '1'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
OPEN_MONEY AS 'MONEY',
'0.00' AS 'AFTER_MONEY',
'0.00' AS 'CASH_MONEY',
'0.00' AS 'SUBSIDY_MONEY',
'--' AS 'ORDERID',
MI_ID,
CREATETIME,
'开户押金' AS 'REMARK',
'加款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_open_account_money_record
WHERE
CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
CARD_COST AS 'MONEY',
'0.00' AS 'AFTER_MONEY',
'0.00' AS 'CASH_MONEY',
'0.00' AS 'SUBSIDY_MONEY',
ORDER_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'换卡押金' AS 'REMARK',
'加款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '0'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
SUBSIDY_MONEY AS 'MONEY',
SUBSIDY_ORIGINALAMOUNT AS 'AFTER_MONEY',
CASH_MONEY,
SUBSIDY_MONEY,
LESS_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'补贴退还' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
CASH_MONEY AS 'MONEY',
CASH_ORIGINALAMOUNT AS 'AFTER_MONEY',
CASH_MONEY,
SUBSIDY_MONEY,
LESS_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'现金退还' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
EXTRACT_MONEY AS 'MONEY',
EXTRACT_AFTER_MONEY AS 'AFTER_MONEY',
CASH_EXTRACT_MONEY AS 'CASH_MONEY',
SUBSIDY_EXTRACT_MONEY AS 'SUBSIDY_MONEY',
SEQNO AS 'ORDERID',
MI_ID,
CREATETIME,
'现金提取' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM #
tf_cash_extract_record
WHERE
CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000'
) record ON record.MI_ID = tf_memberinfo.MI_ID
LEFT JOIN tf_userinfo ON record.U_ID = tf_userinfo.U_ID
WHERE
1 = 1
AND record.MONEY > 0
ORDER BY
record.CREATETIME ASC
) UNION ALL
(
SELECT
'总计' AS 'CREATETIME',
'总计' AS 'MI_NAME',
'--' AS 'MI_PHONE',
'--' AS 'MI_NO',
'--' AS 'OPERATE_MACHINE_NO',
total.MONEY,
total.AFTER_MONEY,
total.CASH_MONEY,
total.SUBSIDY_MONEY,
'汇总' AS 'REMARK',
'汇总' AS 'TYPE',
'--' AS 'CASHIER'
FROM
(
SELECT
record.CREATETIME,
'总计' AS 'MI_NAME',
'--' AS 'MI_PHONE',
'--' AS 'MI_NO',
'--' AS 'OPERATE_MACHINE_NO',
sum( record.MONEY ) AS 'MONEY',
sum( record.AFTER_MONEY ) AS 'AFTER_MONEY',
sum( record.CASH_MONEY ) AS 'CASH_MONEY',
sum( record.SUBSIDY_MONEY ) AS 'SUBSIDY_MONEY',
'汇总' AS 'REMARK',
'汇总' AS 'TYPE',
'--' AS 'CASHIER'
FROM
tf_memberinfo
INNER JOIN (
SELECT
CASH_MONEY AS 'MONEY',
CASH_ORIGINALAMOUNT AS 'AFTER_MONEY',
CASH_MONEY,
SUBSIDY_MONEY,
RECHARGE_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'现金交费' AS 'REMARK',
'加款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_member_recharge_record
WHERE
RECHARGE_TYPE IN ( '0', '4' )
AND REFUND_STATUS = '0'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
CARD_COST AS 'MONEY',
'0.00' AS 'AFTER_MONEY',
'0.00' AS 'CASH_MONEY',
'0.00' AS 'SUBSIDY_MONEY',
ORDER_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'退还押金' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '1'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
OPEN_MONEY AS 'MONEY',
'0.00' AS 'AFTER_MONEY',
'0.00' AS 'CASH_MONEY',
'0.00' AS 'SUBSIDY_MONEY',
'--' AS 'ORDERID',
MI_ID,
CREATETIME,
'开户押金' AS 'REMARK',
'加款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_open_account_money_record
WHERE
CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
CARD_COST AS 'MONEY',
'0.00' AS 'AFTER_MONEY',
'0.00' AS 'CASH_MONEY',
'0.00' AS 'SUBSIDY_MONEY',
ORDER_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'换卡押金' AS 'REMARK',
'加款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '0'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
SUBSIDY_MONEY AS 'MONEY',
SUBSIDY_ORIGINALAMOUNT AS 'AFTER_MONEY',
CASH_MONEY,
SUBSIDY_MONEY,
LESS_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'补贴退还' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
CASH_MONEY AS 'MONEY',
CASH_ORIGINALAMOUNT AS 'AFTER_MONEY',
CASH_MONEY,
SUBSIDY_MONEY,
LESS_ID AS 'ORDERID',
MI_ID,
CREATETIME,
'现金退还' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
AND CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000' UNION ALL
SELECT
EXTRACT_MONEY AS 'MONEY',
EXTRACT_AFTER_MONEY AS 'AFTER_MONEY',
CASH_EXTRACT_MONEY AS 'CASH_MONEY',
SUBSIDY_EXTRACT_MONEY AS 'SUBSIDY_MONEY',
SEQNO AS 'ORDERID',
MI_ID,
CREATETIME,
'现金提取' AS 'REMARK',
'减款' AS 'TYPE',
CLIENT_CODE,
STORE_CODE,
U_ID
FROM
tf_cash_extract_record
WHERE
CREATETIME BETWEEN '2022-05-18 00:00:00'
AND '2022-05-22 23:59:59'
AND CLIENT_CODE = '164939526950419'
AND STORE_CODE = '0000'
) record ON record.MI_ID = tf_memberinfo.MI_ID
LEFT JOIN tf_userinfo ON record.U_ID = tf_userinfo.U_ID
WHERE
1 = 1
AND record.MONEY > 0
) total
)
LIMIT 0,
10
查询结果如下
报表汇总表sql
SELECT
total.DATE,
total.CASH_RECHARGE_MONEY,
total.SUBSIDY_RECHARGE_MONEY,
total.accountMoney,
total.CARD_COST,
( total.CASH_RECHARGE_MONEY + total.SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) AS 'TOL_CASH',
total.RefundToError,
total.REFUND_CARD_COST,
total.EXTRACT_MONEY,
total.refundBalance,
( total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance ) AS 'TOL_SUBSIDY',
(
( total.CASH_RECHARGE_MONEY + SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) - ( total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance + total.TOL_CCR_MONEY )
) AS 'TOTAL_INCREASE',
total.TOL_CCR_MONEY,
(
(
( total.CASH_RECHARGE_MONEY + SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) - ( total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance + total.TOL_CCR_MONEY )
) - total.TOL_CCR_MONEY
) AS 'REMAINING_AMOUNT',
( total.CASH_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) AS 'CASH_PAY'
FROM
(
SELECT
hospital_date_record.DATE,
( CASE WHEN tf_member_recharge_record.CASH_MONEY IS NULL THEN 0.00 ELSE tf_member_recharge_record.CASH_MONEY END ) AS 'CASH_RECHARGE_MONEY',
( CASE WHEN tf_member_recharge_record.SUBSIDY_MONEY IS NULL THEN 0.00 ELSE tf_member_recharge_record.SUBSIDY_MONEY END ) AS 'SUBSIDY_RECHARGE_MONEY',
( CASE WHEN tf_member_less_record.RefundToError IS NULL THEN 0.00 ELSE tf_member_less_record.RefundToError END ) AS 'RefundToError',
( CASE WHEN tf_open_account_money_record.accountMoney IS NULL THEN 0.00 ELSE tf_open_account_money_record.accountMoney END ) AS 'accountMoney',
( CASE WHEN tf_card_cost_record.CARD_COST IS NULL THEN 0.00 ELSE tf_card_cost_record.CARD_COST END ) AS 'CARD_COST',
'0' AS 'payMachine',
( CASE WHEN tf_refund_card_cost_record.REFUND_CARD_COST IS NULL THEN 0.00 ELSE tf_refund_card_cost_record.REFUND_CARD_COST END ) AS 'REFUND_CARD_COST',
( CASE WHEN tf_cash_extract_record.EXTRACT_MONEY IS NULL THEN 0.00 ELSE tf_cash_extract_record.EXTRACT_MONEY END ) AS 'EXTRACT_MONEY',
( CASE WHEN tf_member_less_record_refundBalance.refundBalance IS NULL THEN 0.00 ELSE tf_member_less_record_refundBalance.refundBalance END ) AS 'refundBalance',
( CASE WHEN tf_cash_extract_record.SUBSIDY_MONEY IS NULL THEN 0.00 ELSE tf_cash_extract_record.SUBSIDY_MONEY END ) AS 'SUBSIDY_MONEY',
'0' AS 'refundMachine',
( CASE WHEN totalAddMoney.totalAddMoney IS NULL THEN 0.00 ELSE totalAddMoney.totalAddMoney END ) AS 'totalAddMoney',
( CASE WHEN zxc.TOL_CCR_MONEY IS NULL THEN 0.00 ELSE zxc.TOL_CCR_MONEY END ) AS 'TOL_CCR_MONEY'
FROM
hospital_date_record
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CASH_MONEY ) AS 'CASH_MONEY',
sum( SUBSIDY_MONEY ) AS 'SUBSIDY_MONEY'
FROM
tf_member_recharge_record
WHERE
RECHARGE_TYPE IN ( '0', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_recharge_record ON hospital_date_record.DATE = tf_member_recharge_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( LESS_MONEY ) AS 'RefundToError'
FROM
tf_member_less_record
WHERE
LESS_TYPE IN ( '3', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_less_record ON hospital_date_record.DATE = tf_member_less_record.daydate
LEFT JOIN ( SELECT date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate', sum( OPEN_MONEY ) AS 'accountMoney' FROM tf_open_account_money_record GROUP BY date_format( CREATETIME, '%Y-%m-%d' ) ) tf_open_account_money_record ON hospital_date_record.DATE = tf_open_account_money_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'CARD_COST'
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_card_cost_record ON hospital_date_record.DATE = tf_card_cost_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'REFUND_CARD_COST'
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '1'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_refund_card_cost_record ON hospital_date_record.DATE = tf_refund_card_cost_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CASH_MONEY ) AS 'EXTRACT_MONEY',
sum( SUBSIDY_MONEY ) AS 'refundBalance'
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_less_record_refundBalance ON hospital_date_record.DATE = tf_member_less_record_refundBalance.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( SUBSIDY_EXTRACT_MONEY ) AS 'SUBSIDY_MONEY',
sum( EXTRACT_MONEY ) AS 'EXTRACT_MONEY'
FROM
tf_cash_extract_record
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_cash_extract_record ON hospital_date_record.DATE = tf_cash_extract_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CCR_MONEY ) AS 'CCR_MONEY'
FROM
tf_consume_card_record
WHERE
CCR_PAY_TYPE = '0'
AND CCR_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_consume_card_record ON hospital_date_record.DATE = tf_consume_card_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CCR_MONEY ) AS 'TOL_CCR_MONEY'
FROM
tf_consume_card_record
WHERE
CCR_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) zxc ON hospital_date_record.DATE = zxc.daydate
LEFT JOIN (
SELECT
e.DATE,
( e.RECHARGE_MONEY + e.LESS_MONEY + e.OPEN_MONEY + e.CARD_COST ) AS 'totalAddMoney'
FROM
(
SELECT
hospital_date_record.DATE,
( CASE WHEN a.RECHARGE_MONEY IS NULL THEN 0 ELSE a.RECHARGE_MONEY END ) AS 'RECHARGE_MONEY',
( CASE WHEN b.LESS_MONEY IS NULL THEN 0 ELSE b.LESS_MONEY END ) AS 'LESS_MONEY',
( CASE WHEN c.OPEN_MONEY IS NULL THEN 0 ELSE c.OPEN_MONEY END ) AS 'OPEN_MONEY',
( CASE WHEN d.CARD_COST IS NULL THEN 0 ELSE d.CARD_COST END ) AS 'CARD_COST'
FROM
hospital_date_record
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( RECHARGE_MONEY ) AS 'RECHARGE_MONEY'
FROM
tf_member_recharge_record
WHERE
REFUND_STATUS = '0'
AND RECHARGE_TYPE IN ( '0', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) a ON hospital_date_record.DATE = a.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( LESS_MONEY ) AS 'LESS_MONEY'
FROM
tf_member_less_record
WHERE
LESS_TYPE = '3'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) b ON hospital_date_record.DATE = b.daydate
LEFT JOIN ( SELECT date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate', sum( OPEN_MONEY ) AS 'OPEN_MONEY' FROM tf_open_account_money_record GROUP BY date_format( CREATETIME, '%Y-%m-%d' ) ) c ON hospital_date_record.DATE = c.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'CARD_COST'
FROM
tf_card_cost_record
WHERE
REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) d ON hospital_date_record.DATE = d.daydate
) e
) totalAddMoney ON hospital_date_record.DATE = totalAddMoney.DATE
WHERE
hospital_date_record.DATE BETWEEN '2022-05-19'
AND '2022-05-24'
ORDER BY
hospital_date_record.DATE ASC
) total UNION ALL
SELECT
totalNumber.DATE,
totalNumber.CASH_RECHARGE_MONEY,
totalNumber.SUBSIDY_RECHARGE_MONEY,
totalNumber.accountMoney,
totalNumber.CARD_COST,
totalNumber.TOL_CASH,
totalNumber.RefundToError,
totalNumber.REFUND_CARD_COST,
totalNumber.EXTRACT_MONEY,
totalNumber.refundBalance,
totalNumber.TOL_SUBSIDY,
totalNumber.TOTAL_INCREASE,
totalNumber.TOL_CCR_MONEY,
totalNumber.REMAINING_AMOUNT,
totalNumber.CASH_PAY
FROM
(
SELECT
'上月余额' AS 'DATE',
'--' AS 'CASH_RECHARGE_MONEY',
'--' AS 'SUBSIDY_RECHARGE_MONEY',
'--' AS 'accountMoney',
'--' AS 'CARD_COST',
'--' AS 'TOL_CASH',
'--' AS 'RefundToError',
'--' AS 'REFUND_CARD_COST',
'--' AS 'EXTRACT_MONEY',
'--' AS 'refundBalance',
'--' AS 'TOL_SUBSIDY',
'--' AS 'TOTAL_INCREASE',
'--' AS 'TOL_CCR_MONEY',
sum((
(
( total.CASH_RECHARGE_MONEY + SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) - ( total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance + total.TOL_CCR_MONEY )
) - total.TOL_CCR_MONEY
)) AS 'REMAINING_AMOUNT',
'--' AS 'CASH_PAY'
FROM
(
SELECT
hospital_date_record.DATE,
( CASE WHEN tf_member_recharge_record.CASH_MONEY IS NULL THEN 0.00 ELSE tf_member_recharge_record.CASH_MONEY END ) AS 'CASH_RECHARGE_MONEY',
( CASE WHEN tf_member_recharge_record.SUBSIDY_MONEY IS NULL THEN 0.00 ELSE tf_member_recharge_record.SUBSIDY_MONEY END ) AS 'SUBSIDY_RECHARGE_MONEY',
( CASE WHEN tf_member_less_record.RefundToError IS NULL THEN 0.00 ELSE tf_member_less_record.RefundToError END ) AS 'RefundToError',
( CASE WHEN tf_open_account_money_record.accountMoney IS NULL THEN 0.00 ELSE tf_open_account_money_record.accountMoney END ) AS 'accountMoney',
( CASE WHEN tf_card_cost_record.CARD_COST IS NULL THEN 0.00 ELSE tf_card_cost_record.CARD_COST END ) AS 'CARD_COST',
'0' AS 'payMachine',
( CASE WHEN tf_refund_card_cost_record.REFUND_CARD_COST IS NULL THEN 0.00 ELSE tf_refund_card_cost_record.REFUND_CARD_COST END ) AS 'REFUND_CARD_COST',
( CASE WHEN tf_cash_extract_record.EXTRACT_MONEY IS NULL THEN 0.00 ELSE tf_cash_extract_record.EXTRACT_MONEY END ) AS 'EXTRACT_MONEY',
( CASE WHEN tf_member_less_record_refundBalance.refundBalance IS NULL THEN 0.00 ELSE tf_member_less_record_refundBalance.refundBalance END ) AS 'refundBalance',
( CASE WHEN tf_cash_extract_record.SUBSIDY_MONEY IS NULL THEN 0.00 ELSE tf_cash_extract_record.SUBSIDY_MONEY END ) AS 'SUBSIDY_MONEY',
'0' AS 'refundMachine',
( CASE WHEN totalAddMoney.totalAddMoney IS NULL THEN 0.00 ELSE totalAddMoney.totalAddMoney END ) AS 'totalAddMoney',
( CASE WHEN zxc.TOL_CCR_MONEY IS NULL THEN 0.00 ELSE zxc.TOL_CCR_MONEY END ) AS 'TOL_CCR_MONEY'
FROM
hospital_date_record
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CASH_MONEY ) AS 'CASH_MONEY',
sum( SUBSIDY_MONEY ) AS 'SUBSIDY_MONEY'
FROM
tf_member_recharge_record
WHERE
RECHARGE_TYPE IN ( '0', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_recharge_record ON hospital_date_record.DATE = tf_member_recharge_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( LESS_MONEY ) AS 'RefundToError'
FROM
tf_member_less_record
WHERE
LESS_TYPE IN ( '3', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_less_record ON hospital_date_record.DATE = tf_member_less_record.daydate
LEFT JOIN ( SELECT date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate', sum( OPEN_MONEY ) AS 'accountMoney' FROM tf_open_account_money_record GROUP BY date_format( CREATETIME, '%Y-%m-%d' ) ) tf_open_account_money_record ON hospital_date_record.DATE = tf_open_account_money_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'CARD_COST'
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_card_cost_record ON hospital_date_record.DATE = tf_card_cost_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'REFUND_CARD_COST'
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '1'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_refund_card_cost_record ON hospital_date_record.DATE = tf_refund_card_cost_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CASH_MONEY ) AS 'EXTRACT_MONEY',
sum( SUBSIDY_MONEY ) AS 'refundBalance'
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_less_record_refundBalance ON hospital_date_record.DATE = tf_member_less_record_refundBalance.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CCR_MONEY ) AS 'CCR_MONEY'
FROM
tf_consume_card_record
WHERE
CCR_PAY_TYPE = '0'
AND CCR_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_consume_card_record ON hospital_date_record.DATE = tf_consume_card_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CCR_MONEY ) AS 'TOL_CCR_MONEY'
FROM
tf_consume_card_record
WHERE
CCR_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) zxc ON hospital_date_record.DATE = zxc.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( SUBSIDY_EXTRACT_MONEY ) AS 'SUBSIDY_MONEY',
sum( EXTRACT_MONEY ) AS 'EXTRACT_MONEY'
FROM
tf_cash_extract_record
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_cash_extract_record ON hospital_date_record.DATE = tf_cash_extract_record.daydate
LEFT JOIN (
SELECT
e.DATE,
( e.RECHARGE_MONEY + e.LESS_MONEY + e.OPEN_MONEY + e.CARD_COST ) AS 'totalAddMoney'
FROM
(
SELECT
hospital_date_record.DATE,
( CASE WHEN a.RECHARGE_MONEY IS NULL THEN 0 ELSE a.RECHARGE_MONEY END ) AS 'RECHARGE_MONEY',
( CASE WHEN b.LESS_MONEY IS NULL THEN 0 ELSE b.LESS_MONEY END ) AS 'LESS_MONEY',
( CASE WHEN c.OPEN_MONEY IS NULL THEN 0 ELSE c.OPEN_MONEY END ) AS 'OPEN_MONEY',
( CASE WHEN d.CARD_COST IS NULL THEN 0 ELSE d.CARD_COST END ) AS 'CARD_COST'
FROM
hospital_date_record
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( RECHARGE_MONEY ) AS 'RECHARGE_MONEY'
FROM
tf_member_recharge_record
WHERE
REFUND_STATUS = '0'
AND RECHARGE_TYPE IN ( '0', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) a ON hospital_date_record.DATE = a.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( LESS_MONEY ) AS 'LESS_MONEY'
FROM
tf_member_less_record
WHERE
LESS_TYPE = '3'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) b ON hospital_date_record.DATE = b.daydate
LEFT JOIN ( SELECT date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate', sum( OPEN_MONEY ) AS 'OPEN_MONEY' FROM tf_open_account_money_record GROUP BY date_format( CREATETIME, '%Y-%m-%d' ) ) c ON hospital_date_record.DATE = c.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'CARD_COST'
FROM
tf_card_cost_record
WHERE
REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) d ON hospital_date_record.DATE = d.daydate
) e
) totalAddMoney ON hospital_date_record.DATE = totalAddMoney.DATE
WHERE
hospital_date_record.DATE BETWEEN '1969-01-01'
AND ( SELECT date_sub( date_sub( date_format( now(), '%y-%m-%d' ), INTERVAL extract( DAY FROM now()) DAY ), INTERVAL 0 MONTH ) AS date )
ORDER BY
hospital_date_record.DATE ASC
) total
) totalNumber UNION ALL
SELECT
totalNumber.DATE,
totalNumber.CASH_RECHARGE_MONEY,
totalNumber.SUBSIDY_RECHARGE_MONEY,
totalNumber.accountMoney,
totalNumber.CARD_COST,
totalNumber.TOL_CASH,
totalNumber.RefundToError,
totalNumber.REFUND_CARD_COST,
totalNumber.EXTRACT_MONEY,
totalNumber.refundBalance,
totalNumber.TOL_SUBSIDY,
totalNumber.TOTAL_INCREASE,
totalNumber.TOL_CCR_MONEY,
totalNumber.REMAINING_AMOUNT,
totalNumber.CASH_PAY
FROM
(
SELECT
'总计' AS 'DATE',
sum( total.CASH_RECHARGE_MONEY ) AS 'CASH_RECHARGE_MONEY',
sum( total.SUBSIDY_RECHARGE_MONEY ) AS 'SUBSIDY_RECHARGE_MONEY',
sum( total.accountMoney ) AS 'accountMoney',
sum( total.CARD_COST ) AS 'CARD_COST',
sum((
total.CASH_RECHARGE_MONEY + total.SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST
)) AS 'TOL_CASH',
sum( total.RefundToError ) AS 'RefundToError',
sum( total.REFUND_CARD_COST ) AS 'REFUND_CARD_COST',
sum( total.EXTRACT_MONEY ) AS 'EXTRACT_MONEY',
sum( total.refundBalance ) AS 'refundBalance',
sum((
total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance
)) AS 'TOL_SUBSIDY',
sum((
( total.CASH_RECHARGE_MONEY + SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) - ( total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance + total.TOL_CCR_MONEY )
)) AS 'TOTAL_INCREASE',
sum( total.TOL_CCR_MONEY ) AS 'TOL_CCR_MONEY',
sum((
(
( total.CASH_RECHARGE_MONEY + SUBSIDY_RECHARGE_MONEY + total.accountMoney + total.CARD_COST ) - ( total.RefundToError + total.REFUND_CARD_COST + total.EXTRACT_MONEY + total.refundBalance + total.TOL_CCR_MONEY )
) - total.TOL_CCR_MONEY
)) AS 'REMAINING_AMOUNT',
sum((
total.CASH_RECHARGE_MONEY + total.accountMoney + total.CARD_COST
)) AS 'CASH_PAY'
FROM
(
SELECT
hospital_date_record.DATE,
( CASE WHEN tf_member_recharge_record.CASH_MONEY IS NULL THEN 0.00 ELSE tf_member_recharge_record.CASH_MONEY END ) AS 'CASH_RECHARGE_MONEY',
( CASE WHEN tf_member_recharge_record.SUBSIDY_MONEY IS NULL THEN 0.00 ELSE tf_member_recharge_record.SUBSIDY_MONEY END ) AS 'SUBSIDY_RECHARGE_MONEY',
( CASE WHEN tf_member_less_record.RefundToError IS NULL THEN 0.00 ELSE tf_member_less_record.RefundToError END ) AS 'RefundToError',
( CASE WHEN tf_open_account_money_record.accountMoney IS NULL THEN 0.00 ELSE tf_open_account_money_record.accountMoney END ) AS 'accountMoney',
( CASE WHEN tf_card_cost_record.CARD_COST IS NULL THEN 0.00 ELSE tf_card_cost_record.CARD_COST END ) AS 'CARD_COST',
'0' AS 'payMachine',
( CASE WHEN tf_refund_card_cost_record.REFUND_CARD_COST IS NULL THEN 0.00 ELSE tf_refund_card_cost_record.REFUND_CARD_COST END ) AS 'REFUND_CARD_COST',
( CASE WHEN tf_cash_extract_record.EXTRACT_MONEY IS NULL THEN 0.00 ELSE tf_cash_extract_record.EXTRACT_MONEY END ) AS 'EXTRACT_MONEY',
( CASE WHEN tf_member_less_record_refundBalance.refundBalance IS NULL THEN 0.00 ELSE tf_member_less_record_refundBalance.refundBalance END ) AS 'refundBalance',
( CASE WHEN tf_cash_extract_record.SUBSIDY_MONEY IS NULL THEN 0.00 ELSE tf_cash_extract_record.SUBSIDY_MONEY END ) AS 'SUBSIDY_MONEY',
'0' AS 'refundMachine',
( CASE WHEN totalAddMoney.totalAddMoney IS NULL THEN 0.00 ELSE totalAddMoney.totalAddMoney END ) AS 'totalAddMoney',
( CASE WHEN zxc.TOL_CCR_MONEY IS NULL THEN 0.00 ELSE zxc.TOL_CCR_MONEY END ) AS 'TOL_CCR_MONEY'
FROM
hospital_date_record
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CASH_MONEY ) AS 'CASH_MONEY',
sum( SUBSIDY_MONEY ) AS 'SUBSIDY_MONEY'
FROM
tf_member_recharge_record
WHERE
RECHARGE_TYPE IN ( '0', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_recharge_record ON hospital_date_record.DATE = tf_member_recharge_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( LESS_MONEY ) AS 'RefundToError'
FROM
tf_member_less_record
WHERE
LESS_TYPE IN ( '3', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_less_record ON hospital_date_record.DATE = tf_member_less_record.daydate
LEFT JOIN ( SELECT date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate', sum( OPEN_MONEY ) AS 'accountMoney' FROM tf_open_account_money_record GROUP BY date_format( CREATETIME, '%Y-%m-%d' ) ) tf_open_account_money_record ON hospital_date_record.DATE = tf_open_account_money_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'CARD_COST'
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_card_cost_record ON hospital_date_record.DATE = tf_card_cost_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'REFUND_CARD_COST'
FROM
tf_card_cost_record
WHERE
PAY_STATUS = '1'
AND REFUND_STATUS = '1'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_refund_card_cost_record ON hospital_date_record.DATE = tf_refund_card_cost_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CASH_MONEY ) AS 'EXTRACT_MONEY',
sum( SUBSIDY_MONEY ) AS 'refundBalance'
FROM
tf_member_less_record
WHERE
LESS_TYPE = '1'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_member_less_record_refundBalance ON hospital_date_record.DATE = tf_member_less_record_refundBalance.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CCR_MONEY ) AS 'CCR_MONEY'
FROM
tf_consume_card_record
WHERE
CCR_PAY_TYPE = '0'
AND CCR_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_consume_card_record ON hospital_date_record.DATE = tf_consume_card_record.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CCR_MONEY ) AS 'TOL_CCR_MONEY'
FROM
tf_consume_card_record
WHERE
CCR_STATUS = '1'
AND REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) zxc ON hospital_date_record.DATE = zxc.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( SUBSIDY_EXTRACT_MONEY ) AS 'SUBSIDY_MONEY',
sum( EXTRACT_MONEY ) AS 'EXTRACT_MONEY'
FROM
tf_cash_extract_record
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) tf_cash_extract_record ON hospital_date_record.DATE = tf_cash_extract_record.daydate
LEFT JOIN (
SELECT
e.DATE,
( e.RECHARGE_MONEY + e.LESS_MONEY + e.OPEN_MONEY + e.CARD_COST ) AS 'totalAddMoney'
FROM
(
SELECT
hospital_date_record.DATE,
( CASE WHEN a.RECHARGE_MONEY IS NULL THEN 0 ELSE a.RECHARGE_MONEY END ) AS 'RECHARGE_MONEY',
( CASE WHEN b.LESS_MONEY IS NULL THEN 0 ELSE b.LESS_MONEY END ) AS 'LESS_MONEY',
( CASE WHEN c.OPEN_MONEY IS NULL THEN 0 ELSE c.OPEN_MONEY END ) AS 'OPEN_MONEY',
( CASE WHEN d.CARD_COST IS NULL THEN 0 ELSE d.CARD_COST END ) AS 'CARD_COST'
FROM
hospital_date_record
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( RECHARGE_MONEY ) AS 'RECHARGE_MONEY'
FROM
tf_member_recharge_record
WHERE
REFUND_STATUS = '0'
AND RECHARGE_TYPE IN ( '0', '4' )
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) a ON hospital_date_record.DATE = a.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( LESS_MONEY ) AS 'LESS_MONEY'
FROM
tf_member_less_record
WHERE
LESS_TYPE = '3'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) b ON hospital_date_record.DATE = b.daydate
LEFT JOIN ( SELECT date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate', sum( OPEN_MONEY ) AS 'OPEN_MONEY' FROM tf_open_account_money_record GROUP BY date_format( CREATETIME, '%Y-%m-%d' ) ) c ON hospital_date_record.DATE = c.daydate
LEFT JOIN (
SELECT
date_format( CREATETIME, '%Y-%m-%d' ) AS 'daydate',
sum( CARD_COST ) AS 'CARD_COST'
FROM
tf_card_cost_record
WHERE
REFUND_STATUS = '0'
GROUP BY
date_format( CREATETIME, '%Y-%m-%d' )
) d ON hospital_date_record.DATE = d.daydate
) e
) totalAddMoney ON hospital_date_record.DATE = totalAddMoney.DATE
WHERE
hospital_date_record.DATE BETWEEN '2022-05-19'
AND '2022-05-24'
ORDER BY
hospital_date_record.DATE ASC
) total
) totalNumber
LIMIT 0,
10
查询结果如下：
4、遇到的问题和收获：
这次主要练习的还是表的逻辑关系，还有mysql中的left join 和 union all 还有as 取别名的灵活使用
1）、union all 的前后两段sql查询的字段数量必须一致
2）mysql获取的一些日期
#获取当前日期
select curdate();
#获取当月最后一天
select last_day(curdate())
#获取本月的第一天
select date_add(curdate(),interval -day(curdate())+1 day) ;
#获取下个月的第一天
select date_add(curdate() - day(curdate()) +1,interval 1 month );
#获取当前月已过了几天
select day(curdate());
#获取当前月的天数(先加一个月，再减今天是第几天，得到当前月的最后一天，最后求最后一天是几号)
select day(date_add( date_add(curdate(),interval 1 month),interval -day(curdate()) day ));
#上个月的第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now())-1 day),interval 1 month)
#上个月的最后一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now()) day),interval 0 month) as date
#这个月的第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now())-1 day),interval 0 month)
#获取当月最后一天
select last_day(curdate())
4、最后：
这两张表的sql一张600多行一张350多行，真的事搞得头都大了，写到Java代码里去就1000行，还是第一次完成这么长的sql，我相信经历这次需求，mysql方面的进步还是有一点的，当以后遇到复杂繁琐的一大段sql的时候不会像以前一样无从下手了， 不会给自己设置一个无法完成的提示了，挑战难度更大需求的阈值也应该更高了，不会再看见sql一长就退缩了，毕竟本人也是写过600行复杂sql的人了嘛，哈哈哈哈哈哈
