CREATE VIEW `v_noprovide` AS SELECT * FROM tb_section WHERE provide = '0' CREATE VIEW `v_thismonth` AS SELECT ts.userId AS id, CONCAT( 'thismonth:', SUM(ts.amount), 'yuan' ) AS 'thismonth' FROM tb_section AS ts WHERE ts.yearMonth = DATE_FORMAT(NOW(), '%Y%m') CREATE VIEW `v_lastmonth` AS SELECT ts.userId AS id, CONCAT( 'lastmonth:', SUM(ts.amount), 'yuan' ) AS 'lastmonth' FROM tb_section AS ts WHERE ts.yearMonth = DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y%m' ) CREATE VIEW `v_usermanage` AS SELECT u.id AS id, u.email AS email, u.`name` AS `name`, CONCAT( COALESCE ( la.lastmonth, 'lastmonth:0yuan' ), COALESCE ( th.thismonth, 'thismonth:0yuan' ) ) AS recent, Sum(COALESCE(b.amount,0)) AS totalBonus, Sum(COALESCE(n.amount,0)) AS unbilled FROM tb_user AS u LEFT JOIN tb_bonus AS b ON u.id = b.receiverId LEFT JOIN v_noprovide AS n ON u.id = n.userId LEFT JOIN v_thismonth AS th ON u.id = th.id LEFT JOIN v_lastmonth AS la ON u.id = la.id GROUP BY u.id