有三张表账单表bill充值表recharge房间表room其中room的id是bill和recharge的外键。room表中的real_balance表示房间当前余额。账单中存在water_meter_id表示当前账单属于用水账单、electric_meter_id表示用电账单现通过完成一个视图实现对已结算账单金额、总充值金额、总欠费金额、用水账单金额和用电账单金额的统计

CREATE VIEW bill_statistic AS SELECT SUM(CASE WHEN bill.status = '已结算' THEN bill.amount ELSE 0 END) AS settled_amount, SUM(recharge.amount) AS total_recharge_amount, SUM(CASE WHEN bill.status = '未结算' THEN room.real_balance ELSE 0 END) AS total_arrears_amount, SUM(CASE WHEN bill.water_meter_id IS NOT NULL THEN bill.amount ELSE 0 END) AS water_bill_amount, SUM(CASE WHEN bill.electric_meter_id IS NOT NULL THEN bill.amount ELSE 0 END) AS electric_bill_amount FROM bill JOIN room ON room.id = bill.room_id JOIN recharge ON recharge.room_id = room.id