Mysql多个子查询多个LEFTJOIN视图创建_MySQL

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