>데이터 베이스 >MySQL 튜토리얼 >Mysql多个子查询多个LEFTJOIN视图创建_MySQL

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

WBOY
WBOY원래의
2016-06-01 13:18:471219검색

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
성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.