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