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

CREATE VIEW bill_statistics AS SELECT SUM(CASE WHEN b.is_settled = 1 THEN b.amount ELSE 0 END) AS settled_amount, SUM(CASE WHEN b.is_settled = 0 THEN b.amount ELSE 0 END) AS unsettled_amount, SUM(r.amount) AS total_recharge_amount, SUM(CASE WHEN rm.water_meter_id IS NOT NULL THEN b.amount ELSE 0 END) AS water_bill_amount, SUM(CASE WHEN rm.electric_meter_id IS NOT NULL THEN b.amount ELSE 0 END) AS electric_bill_amount, SUM(CASE WHEN rm.water_meter_id IS NULL AND rm.electric_meter_id IS NULL THEN b.amount ELSE 0 END) AS other_bill_amount FROM bill b LEFT JOIN recharge r ON b.room_id = r.room_id LEFT JOIN room rm ON b.room_id = rm.id