bitsCN.com
CREATE VIEW `v_noprovide` AS SELECT *FROM tb_sectionWHERE provide = '0'CREATE VIEW `v_thismonth` AS SELECT ts.userId AS id, CONCAT( 'thismonth:', SUM(ts.amount), 'yuan' ) AS 'thismonth'FROM tb_section AS tsWHERE 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 tsWHERE ts.yearMonth = DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y%m' )CREATE VIEW `v_usermanage` ASSELECT 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 unbilledFROM tb_user AS uLEFT JOIN tb_bonus AS b ON u.id = b.receiverIdLEFT JOIN v_noprovide AS n ON u.id = n.userIdLEFT JOIN v_thismonth AS th ON u.id = th.idLEFT JOIN v_lastmonth AS la ON u.id = la.idGROUP BY u.id
bitsCN.com